Mysql UNION or something else to get debit and credit totals for different accounts

Example, placed here http://sqlfiddle.com/#!9/1b4e73c/5 and
http://sqlfiddle.com/#!9/b9af22/1

Have table accounts with list of accounts. And have table transactions with list of all transactions.

In accounts table are following accounts: 1210, 2380, 5310, 7720.

In transactions table are following transactions:

DebitAccount | CreditAccount | Amount 

1210         | 5310          | 10.5 

7720         | 2380          | 4.6

As result want to get array like this:

[0] => Array
    (
        [AccountCode] => 1210
        [DebitTotal] => 10.5
        [CreditTotal] => 0
    )

[1] => Array
    (
        [AccountCode] => 5310
        [DebitTotal] => 0
        [CreditTotal] => 10.5
    )
[2] => Array
    (
        [AccountCode] => 7720
        [DebitTotal] => 4.6
        [CreditTotal] => 0
    )
[3] => Array
    (
        [AccountCode] => 2380
        [DebitTotal] => 0
        [CreditTotal] => 4.6
    )

If i select separately, like

SELECT 
`COA`.`AccountCode`, 
SUM(`JL`.`Amount`) AS `DebitTotal`
FROM `transactions` `JL` 
LEFT OUTER JOIN `accounts` `COA` ON `JL`.`DebitAccount` = `COA`.`AccountCode`
GROUP BY `COA`.`AccountCode`;

Then all ok. But how to unify ?

Incorrect code is this

SELECT `AccountCode`, `DebitTotal`, `CreditTotal` 

FROM 

( 
SELECT 
`COA`.`AccountCode`, 
SUM(`JL`.`Amount`) AS `DebitTotal`
FROM `transactions` `JL` 
LEFT OUTER JOIN `accounts` `COA` ON `JL`.`DebitAccount` = `COA`.`AccountCode`
GROUP BY `COA`.`AccountCode` 

UNION  

SELECT
`COA`.`AccountCode`, 
SUM(`JL`.`Amount`) AS `CreditTotal`
FROM `transactions` `JL` 
LEFT OUTER JOIN `accounts` `COA` ON `JL`.`CreditAccount` = `COA`.`AccountCode`
GROUP BY `COA`.`AccountCode` 

) `TempTable`

Get error Unknown column 'CreditTotal' in 'field list'

Problem is that Debit and Credit account numbers may be not the same. I mean in Debit exists some account, but in Credit the account does not exist….

I tried to select all accounts from accounts table. Then php foreach all accounts and created long sql to select from transactions table. But seems that is a waste of resources… Trying to do all with one query.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

You need to make CreditTotal the third column in the second query in the UNION. Add empty columns to both subqueries for the sum in the other query.

SELECT AccountCode, SUM(Debit) AS DebitTotal, SUM(Credit) AS CreditTotal
FROM (
    SELECT DebitAccount AS AccountCode, Amount AS Debit, 0 AS Credit
    FROM transactions
    UNION ALL
    SELECT CreditAccount AS AccountCode, 0 AS Debit, Amount AS Credit
    FROM transactions
) AS u
GROUP BY AccountCode

There’s no need to join with accounts, since you’re not using any columns from that table.

Method 2

You could use a union approach here:

SELECT
    a.AccountCode,
    SUM(CASE WHEN t.Amount < 0 THEN t.Amount ELSE 0 END) AS DebitTotal,
    SUM(CASE WHEN t.Amount > 0 THEN t.Amount ELSE 0 END) AS CreditTotal
FROM accounts a
LEFT JOIN
(
    SELECT DebitAccount AS Account, -1.0*Amount AS Amount FROM transactions
    UNION ALL
    SELECT CreditAccount, Amount FROM transactions
) t
    ON t.Account = a.AccountCode
GROUP BY a.AccountCode;


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x