SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    Quake 1 Addict CreedFeed's Avatar
    Join Date
    Feb 2002
    Location
    Milwaukee, WI
    Posts
    296
    Mentioned
    0 Post(s)
    Tagged
    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...

    cart
    id bunch of other fields
    1 ......
    2 ......

    cart_items
    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:

    Code:
       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?
    -- Steve Caponetto
    Quake 1 Resurrection :: CreedFeed

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by CreedFeed View Post
    Basically I need the group by details to show up in every single line? Is this possible?
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    697
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    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. #4
    Quake 1 Addict CreedFeed's Avatar
    Join Date
    Feb 2002
    Location
    Milwaukee, WI
    Posts
    296
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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
    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?
    -- Steve Caponetto
    Quake 1 Resurrection :: CreedFeed

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by CreedFeed View Post
    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?
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    Quake 1 Addict CreedFeed's Avatar
    Join Date
    Feb 2002
    Location
    Milwaukee, WI
    Posts
    296
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    this is correct, but i assure you that a detail query and a totals query on those same details is feasible and easy
    How would you accomplish this using two queries and union'ing them? I'm all about learning more...
    -- Steve Caponetto
    Quake 1 Resurrection :: CreedFeed

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by CreedFeed View Post
    How would you accomplish this using two queries and union'ing them? I'm all about learning more...
    query results
    id cart_id category item qty price A_totals non_A_totals cart_totals
    NULL 1 NULL NULL NULL NULL 75.00 14.00 89.00
    1 1 A widget1 5 10.00 NULL NULL NULL
    2 1 A widget2 1 25.00 NULL NULL NULL
    3 1 B widget3 2 2.50 NULL NULL NULL
    4 1 C widget4 1 9.00 NULL NULL NULL
    NULL 2 NULL NULL NULL NULL 333.00 31.00 364.00
    7 2 A doodad1 9 37.00 NULL NULL NULL
    8 2 B thingum 2 8.00 NULL NULL NULL
    9 2 C whatsit 1 15.00 NULL NULL NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    Quake 1 Addict CreedFeed's Avatar
    Join Date
    Feb 2002
    Location
    Milwaukee, WI
    Posts
    296
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    query results
    Would you just be adding a NULL for each field that isn't applicable to each query to be UNION'd, like this:

    Code:
       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
    -- Steve Caponetto
    Quake 1 Resurrection :: CreedFeed

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    that's correct, just like that

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

    ORDER BY cart_id, id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    Quake 1 Addict CreedFeed's Avatar
    Join Date
    Feb 2002
    Location
    Milwaukee, WI
    Posts
    296
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!
    -- Steve Caponetto
    Quake 1 Resurrection :: CreedFeed


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •