Confusing query - my brain hurts!
Posted: Mon Oct 03, 2005 10:38 am
The query I'm trying to do involves totalling sales by month or week for a product or products.
The compicated thing is the results I am looking for will feature an undefined number of products and an undefined number of months/weeks. I can't figure out how to do it in 1 query, and have been doing 1 query to get the relavent products, and a query for each product to get the sums of the sales for date ranges, like so:
and for each product:
So i'm putting the sales totals for each product into an array.
The down side of this approach is that a given product matching the search criteria may not have an results for a particular month. So 1 product might return 6 rows, while another returns 2 rows. Meaning that at some point I have to manipulate the results to display in a coherent fashion.
What I really want is a query that will handle results like this:
productID | january | february | march | april | may
HY1 | 5 | 3 | 7 | 0 | 0
or this:
productID | january | february | march
HY1 | 5 | 3 | 7
HY67 | 0 | 0 | 5
if you see what I mean. Is this possible to have a query with a flexible number of fields? Or can someone suggest how best to approach this?
I'm dead confused!!!
The compicated thing is the results I am looking for will feature an undefined number of products and an undefined number of months/weeks. I can't figure out how to do it in 1 query, and have been doing 1 query to get the relavent products, and a query for each product to get the sums of the sales for date ranges, like so:
Code: Select all
SELECT prodCode, productID from tProducts WHERE prodCode LIKE 'hy1%'Code: Select all
select
from_unixtime(uts,'%m %Y') as date,
sum(prodQty) as quantity
from
tOrders o
inner join
tOrderPOs pos
on o.orderID = pos.orderID
inner join
tOrderProds p
on pos.purchaseOrder = p.purchaseOrder
where
productID = 45
and uts > 1125702000
and uts <= 1128380400
group by
from_unixtime(uts,'%m %Y')The down side of this approach is that a given product matching the search criteria may not have an results for a particular month. So 1 product might return 6 rows, while another returns 2 rows. Meaning that at some point I have to manipulate the results to display in a coherent fashion.
What I really want is a query that will handle results like this:
productID | january | february | march | april | may
HY1 | 5 | 3 | 7 | 0 | 0
or this:
productID | january | february | march
HY1 | 5 | 3 | 7
HY67 | 0 | 0 | 5
if you see what I mean. Is this possible to have a query with a flexible number of fields? Or can someone suggest how best to approach this?
I'm dead confused!!!