MySQL Forums
Forum List  »  Oracle

Totaling lines
Posted by: Tyler Brocious
Date: December 29, 2010 10:15AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Totaling lines
3198
December 29, 2010 10:15AM


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.