Sum and Total Query

[josh2780]

What's the proper way to query for a total for a value and also the total for all values for a particular set of parameters?

For example, I have a table of orders that customer service reps make. The reps place multiple orders per day for various products. I'm trying to pull a report that displays the customer service ID (csID), total number of a particular product sold, and the total of all products sold... within a date range.

Sample Orders Table (heavily snipped):


ORDER_ID CS_ID PRO_ID QTY_SOLD DATE
-------- ----- ------ -------- ---------
1 10 105 3 2006-07-08
2 12 105 4 2006-07-10
3 10 105 3 2006-07-10
3 10 120 2 2006-07-10
4 12 105 1 2006-07-11

When querying for the totals between July 8-July 11, the query should return something like:


CS_ID PRO_ID PRO_TOTAL ALL_TOTAL
----- ------ --------- ---------
10 105 6 8
10 120 2 8
12 105 5 5

I've tried various queries that work when I sum up individually, but when I include 2 sums for a row I'm getting duplicates and the sums are too high.

This seems like it would be fairly straightforward but apparently I'm overlooking a key item.

Oracle has a handy function: OVER() and I could do something like:

SELECT cs_id, pro_id,
SUM (qty_sold) pro_total,
SUM (SUM (qty_sold)) OVER (PARTITION BY cs_id) all_total
FROM orders
WHERE dt >= TO_DATE ('06-JUL-08', 'YY-MON-DD')
AND dt < TO_DATE ('06-JUL-11', 'YY-MON-DD') + 1
GROUP BY cs_id, pro_id

but this doesn't work in mysql.



Edited 1 time(s). Last edit at 07/16/2006 02:03PM by josh2780.