Results 1 to 9 of 9
  1. #1
    Join Date
    May 2012
    Posts
    5

    Unanswered: sum of group by two columns

    hi

    i have a data base of 2 tables

    1-members(memberid, membername, groupid)

    2-bills(billid ,memberid,billamount)



    bill is joined to members on memberid

    and members is joined to it self that some members are in a group of an other member so the child members groupid is joined to the parents member memberid

    i want to write a query to get the total bill of each member

    so i wrote like this

    select memberid sum(billamount) from members join bill group by memberid

    but that's not good enough because i want to get the child members total included in the parents total and i can't figure how to do that

    can somebody help me

    if possible with out cte

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Hi,

    Can you post a sample of data from tables and the expected result?

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT memberid 
         , COALESCE(gb.sum_bills,0) +  
           COALESCE(cb.sum_bills,0) AS total_bills 
      FROM members AS g
    LEFT OUTER
      JOIN ( SELECT memberid
                  , SUM(billamount) AS sum_bills
               FROM bills 
             GROUP
                 BY memberid ) AS gb
        ON gb.memberid = g.memberid  
    LEFT OUTER
      JOIN ( SELECT memberid
                  , SUM(billamount) AS sum_bills 
               FROM bills 
             GROUP
                 BY memberid ) AS cb
        ON cb.groupid = g.memberid
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    May 2012
    Posts
    5
    members
    memberid name groupid
    1 a null
    2 c 1
    3 d 1

    bills
    billid bilmemberid amount
    1 1 10.00
    2 1 10.00
    3 2 5.00
    4 3 3.00

    member 2 and 3 are in the account of member 1 so i want to get the sum of bill for the account of member that should be 28

  5. #5
    Join Date
    May 2012
    Posts
    5
    r937
    your solution does not work for me

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jacobgold View Post
    r937
    your solution does not work for me
    jacobgold
    gee, i wonder why
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2012
    Posts
    213
    I believe it is necessary to add a join at the end:

    Code:
    SELECT memberid 
         , COALESCE(gb.sum_bills,0) +  
           COALESCE(cb.sum_bills,0) AS total_bills 
      FROM members AS g
    LEFT OUTER
      JOIN ( SELECT bilmemberid
                  , SUM(amount) AS sum_bills
               FROM bills 
               WHERE groupid is null
             GROUP
                 BY bilmemberid ) AS gb
        ON gb.bilmemberid = g.memberid  
    LEFT OUTER
      JOIN ( SELECT m.groupid
                  , SUM(amount) AS sum_bills 
               FROM bills as b
               JOIN members as m on m.memberid = b.bilmemberid
               WHERE m.groupid is not null
             GROUP
                 BY m.groupid ) AS cb
        ON cb.groupid = g.memberid
    Hope this helps.

  8. #8
    Join Date
    May 2012
    Posts
    5
    THIS SEEMS TO WORK thanks a million imex
    its just like your imex i just omitted the where in the first subquery because the groupid is not in the bill table only in the members table

    Code:
    SELECT memberid 
         , COALESCE(gb.sum_bills,0) +  
           COALESCE(cb.sum_bills,0) AS total_bills 
      FROM members AS g
    LEFT OUTER
      JOIN ( SELECT billmemberid
                  , SUM(billamount) AS sum_bills
               FROM bill
          GROUP BY billmemberid ) AS gb
        ON gb.billmemberid = g.memberid  
    LEFT OUTER
      JOIN ( SELECT m.groupid
                  , SUM(billamount) AS sum_bills 
               FROM bill as b
               JOIN members as m on m.memberid = b.billmemberid
               WHERE m.groupid is not null
             GROUP
                 BY m.groupid ) AS cb
        ON cb.groupid = g.memberid
    Last edited by jacobgold; 05-16-12 at 18:41.

  9. #9
    Join Date
    Apr 2012
    Posts
    213
    Do not forget that most of the work was done by R937.

Posting Permissions

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