CODEWITHSUNDEEP
mysql
user2360831
user2360831

Reputation: 367

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.

Upvotes: 0

Views: 78

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522292

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;

Upvotes: 1

Barmar
Barmar

Reputation: 781994

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.

Upvotes: 2

Related Questions