Results 1 to 10 of 10
Feb 14, 2013, 07:26 #1
- Join Date
- Feb 2002
- Milwaukee, WI
- 0 Post(s)
- 0 Thread(s)
Select GROUP BY sums AND individual lines in one query?
Lets say I have two tables, cart and cart_items. This is all via MySQL...
id bunch of other fields 1 ...... 2 ......
id cart_id category item qty price 1 1 A widget1 5 10.00 2 1 A widget2 1 25.00 3 1 B widget3 2 2.50 4 1 C widget4 1 9.00
Every cart has a list of items with an assigned category (A, B, C, etc.). What I'd like to do is get sums of all category A items, sums of all other categories (B, C, etc.) and then all individual items:
cart_id A_totals non_A_totals cart_totals item qty price ext price category 1 75.00 14.00 89.00 widget1 5 10.00 50.00 A 1 75.00 14.00 89.00 widget2 1 25.00 50.00 A 1 75.00 14.00 89.00 widget3 2 2.50 5.00 B 1 75.00 14.00 89.00 widget4 1 9.00 9.00 C
What I'm unsure of is how to basically do a group by to select the totals for a given cart, but also include the individual lines that are summed up for that group. Basically I need the group by details to show up in every single line? Is this possible?
I can do this using two queries - one to select the totals, and one to select the individual items but I'm not sure if and how to do this in one.
Here's how I'm currently selecting the totals:
SELECT c.id , SUM(IF(ci.category = 'A', ci.qty * ci.price, 0) AS A_totals , SUM(IF(ci.category = 'A', 0, ci.qty * ci.price) AS non_A_totals , SUM(ci.qty * ci.price) AS cart_totals FROM cart c LEFT JOIN cart_items ci ON c.id = ci.cart_id GROUP BY c.id