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 2008-07-06 2 12 105 4 2008-07-10 3 10 105 3 2008-07-10 3 10 120 2 2008-07-10 4 12 105 1 2008-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 OVER() function that would work... Any thoughts? __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
