Select GROUP BY sums AND individual lines in one query?

Databases
1

Lets say I have two tables, cart and cart_items. This is all via MySQL…

cart
[table=“width: 300, class: grid, align: left”]
[tr]
[td]id[/td]
[td]bunch of other fields[/td]
[/tr]
[tr]
[td]1[/td]
[td]…[/td]
[/tr]
[tr]
[td]2[/td]
[td]…[/td]
[/tr]
[/table]

cart_items
[table=“width: 300, class: grid, align: left”]
[tr]
[td]id[/td]
[td]cart_id[/td]
[td]category[/td]
[td]item[/td]
[td]qty[/td]
[td]price[/td]
[/tr]
[tr]
[td]1[/td]
[td]1[/td]
[td]A[/td]
[td]widget1[/td]
[td]5[/td]
[td]10.00[/td]
[/tr]
[tr]
[td]2[/td]
[td]1[/td]
[td]A[/td]
[td]widget2[/td]
[td]1[/td]
[td]25.00[/td]
[/tr]
[tr]
[td]3[/td]
[td]1[/td]
[td]B[/td]
[td]widget3[/td]
[td]2[/td]
[td]2.50[/td]
[/tr]
[tr]
[td]4[/td]
[td]1[/td]
[td]C[/td]
[td]widget4[/td]
[td]1[/td]
[td]9.00[/td]
[/tr]
[/table]

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:

[table=“class: grid, align: left”]
[tr]
[td]cart_id[/td]
[td]A_totals[/td]
[td]non_A_totals[/td]
[td]cart_totals[/td]
[td]item[/td]
[td]qty[/td]
[td]price[/td]
[td]ext price[/td]
[td]category[/td]
[/tr]
[tr]
[td]1[/td]
[td]75.00[/td]
[td]14.00[/td]
[td]89.00[/td]
[td]widget1[/td]
[td]5[/td]
[td]10.00[/td]
[td]50.00[/td]
[td]A[/td]
[/tr]
[tr]
[td]1[/td]
[td]75.00[/td]
[td]14.00[/td]
[td]89.00[/td]
[td]widget2[/td]
[td]1[/td]
[td]25.00[/td]
[td]50.00[/td]
[td]A[/td]
[/tr]
[tr]
[td]1[/td]
[td]75.00[/td]
[td]14.00[/td]
[td]89.00[/td]
[td]widget3[/td]
[td]2[/td]
[td]2.50[/td]
[td]5.00[/td]
[td]B[/td]
[/tr]
[tr]
[td]1[/td]
[td]75.00[/td]
[td]14.00[/td]
[td]89.00[/td]
[td]widget4[/td]
[td]1[/td]
[td]9.00[/td]
[td]9.00[/td]
[td]C[/td]
[/tr]
[/table]

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

Any help?

2

yes, it’s possible, but before i give suggestions, would you mind please answering one question?

why do you want the group by totals to show up in every single line?

it’s actually more efficient to do it with two queries (details and totals) unioned together, than to stick the totals on every line of details

3 

select c.id,
       coalesce(dt_a.A_totals,0) as A_totals,
       coalesce(dt_non_a.non_A_totals,0) as non_A_totals,
       coalesce(dt_a.A_totals,0) + coalesce(dt_non_a.non_A_totals,0) as cart_totals,
       ci.item,
       ci.qty,
       ci.price,
       ci.price*ci.qty as "ext price",
       ci.category
  from cart c
  left
  join cart_items ci
    on c.id = ci.cart_id
  left
  join (select sum(qty*ci.price) as A_totals,
          cart_id
          from cart_items
         where ci.category = 'A'
         group
            by cart_id) dt_a
    on dt_a.cart_id = ci.cart_id
  left
  join (select sum(qty*ci.price) as non_A_totals,
          cart_id
          from cart_items
         where ci.category <> 'A'
         group
            by cart_id) dt_non_a
    on dt_non_a.cart_id = ci.cart_id
4

I’m trying to create a spreadsheet of this information without any programming intervention needed. I did not know you could accomplish this via UNION’d queries? Can you provide an example of how to UNION the two queries together? I was under the assumption that UNION’d queries must be selecting the same fields and you couldn’t combines results from query A and B if each were selecting different fields?

5

this is correct, but i assure you that a detail query and a totals query on those same details is feasible and easy

meanwhile swampboogie gave you what you asked for

6

How would you accomplish this using two queries and union’ing them? I’m all about learning more…

7

query results
[table=“width: 300, class: grid, align: left”]
[tr]
[td]id[/td]
[td]cart_id[/td]
[td]category[/td]
[td]item[/td]
[td]qty[/td]
[td]price[/td]
[td]A_totals[/td]
[td]non_A_totals[/td]
[td]cart_totals[/td]
[/tr]
[tr]
[td]NULL[/td]
[td]1[/td]
[td]NULL[/td]
[td]NULL[/td]
[td]NULL[/td]
[td]NULL[/td]
[td]75.00[/td]
[td]14.00[/td]
[td]89.00[/td]
[/tr]
[tr]
[td]1[/td]
[td]1[/td]
[td]A[/td]
[td]widget1[/td]
[td]5[/td]
[td]10.00[/td]
[td]NULL[/td]
[td]NULL[/td]
[td]NULL[/td]
[/tr]
[tr]
[td]2[/td]
[td]1[/td]
[td]A[/td]
[td]widget2[/td]
[td]1[/td]
[td]25.00[/td]
[td]NULL[/td]
[td]NULL[/td]
[td]NULL[/td]
[/tr]
[tr]
[td]3[/td]
[td]1[/td]
[td]B[/td]
[td]widget3[/td]
[td]2[/td]
[td]2.50[/td]
[td]NULL[/td]
[td]NULL[/td]
[td]NULL[/td]
[/tr]
[tr]
[td]4[/td]
[td]1[/td]
[td]C[/td]
[td]widget4[/td]
[td]1[/td]
[td]9.00[/td]
[td]NULL[/td]
[td]NULL[/td]
[td]NULL[/td]
[/tr]
[tr]
[td]NULL[/td]
[td]2[/td]
[td]NULL[/td]
[td]NULL[/td]
[td]NULL[/td]
[td]NULL[/td]
[td]333.00[/td]
[td]31.00[/td]
[td]364.00[/td]
[/tr]
[tr]
[td]7[/td]
[td]2[/td]
[td]A[/td]
[td]doodad1[/td]
[td]9[/td]
[td]37.00[/td]
[td]NULL[/td]
[td]NULL[/td]
[td]NULL[/td]
[/tr]
[tr]
[td]8[/td]
[td]2[/td]
[td]B[/td]
[td]thingum[/td]
[td]2[/td]
[td]8.00[/td]
[td]NULL[/td]
[td]NULL[/td]
[td]NULL[/td]
[/tr]
[tr]
[td]9[/td]
[td]2[/td]
[td]C[/td]
[td]whatsit[/td]
[td]1[/td]
[td]15.00[/td]
[td]NULL[/td]
[td]NULL[/td]
[td]NULL[/td]
[/tr]
[/table]

8

Would you just be adding a NULL for each field that isn’t applicable to each query to be UNION’d, like this:


   SELECT NULL as id,
        , c.id AS cart_id
        , NULL AS category
        , NULL AS item
        , NULL AS qty
        , NULL AS price
        , 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
9

that’s correct, just like that

next, add the NULL columns to the detail query, and UNION them together

ORDER BY cart_id, id

10

Got it, thank you r937! That makes sense then. I wasn’t sure if there was more to it than that.

And thanks to swampBoogie for the original answer. I tried this method and it worked fine!