CODEWITHSUNDEEP
mysqlsum
Kurt Hectic
Kurt Hectic

Reputation: 37

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

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

Upvotes: 0

Views: 111

Answers (1)

user1898027
user1898027

Reputation: 340

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

Upvotes: 1

Related Questions