Sum and Total Query
Posted by:
josh2780
Date: July 16, 2006 02:01PM
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.
Subject
Views
Written By
Posted
Sum and Total Query
12376
July 16, 2006 02:01PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.