MySQL multiple sums in select statement depending on boolean value in different field

Question

Why is this query not giving me the correct totals ?

     Select H.AccountNo,IF(G.Credit=0,SUM(F.Charge),NULL) AS Debits,IF(G.Credit=1,SUM(F.Charge),NULL) AS Credits
     from transdetails F
       left join transhead G on F.Transhead=G.Seqno
       left join Accounts H on H.SeqNo=G.Account
     Group by H.AccountNo

I am trying to list Accounts with both their charges as well as payments on a single line and the only difference between charges and payments is the field Credits which is a boolean either 1 or 0

Still not giving me the correct values because I know that there are payments for account 000223 Thus Credits cannot be 0

    H.Accounts  Debits     Credits
    000221      131.00     NULL
    000222      287.70     NULL
    000223      70980.20   NULL
    000224      13280.42   NULL

Provide sample data and results so we know what you mean by "not . . . correct totals". — Gordon Linoff
– Gordon Linoff, Commented Mar 3, 2017 at 12:06
@ Gordon Linoff Still not giving me the correct values because I know that there are payments for account 000223 Thus Credits cannot be 0 'H.Accounts Debits Credits 000221 131.00 NULL 000222 287.70 NULL 000223 70980.20 NULL 000224 13280.42 NULL' — Kurt Hectic
– Kurt Hectic, Commented Mar 3, 2017 at 12:16

user1898027 · Accepted Answer · 2017-03-03 12:16:45Z

1

Try this instead

 Select H.AccountNo,SUM(IF(G.Credit=0,F.Charge,0)) AS Debits,SUM(IF(G.Credit=1,F.Charge,0)) AS Credits
 from transdetails F
 left join transhead G on F.Transhead=G.Seqno
 left join Accounts H on H.SeqNo=G.Account
 Group by H.AccountNo

answered Mar 3, 2017 at 12:16

user1898027

3403 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kurt Hectic Over a year ago

Brilliant !! Thanks !!

Collectives™ on Stack Overflow

MySQL multiple sums in select statement depending on boolean value in different field

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related