CODEWITHSUNDEEP
mysql
user2360831
user2360831

Reputation: 367

Mysql Subtract SUM(Amount)DebitAccount from SUM(Amount)CreditAccount if DebitAccount == CreditAccount

Need to subtract credit totals from debit totals where credit and debit account number is the same. If a value exists only for debit account number, nothing to subtract, just need to get the value.

One table called 18_7_ChartOfAccounts looks like this:

ID | AccountNumber
-------------
1  | 2310
2  | 2380

Another table called 2_1_journal looks like this:

ID | Amount | DebitAccount | CreditAccount
-------------------------------------------
1  | 25.00  | 2310         | 2380
2  | 200.00 | 2310         | 2380
3  | 4.00   | 2380         | 2310
4  | 200.00 | 2380         | 2310
5  | 50.00  | 2380         | 2620

Based on advices created query that sums all values in column Amount where value in DebitAccount is the same.

Query does the following: SUM(Amount) FROM table 2_1_journal WHERE DebitAccount in table 2_1_journal is the same as AccountNumber in table 18_7_ChartOfAccounts

$query_debit_beginning = "SELECT SUM(j.Amount), j.DebitAccount FROM 18_7_ChartOfAccounts AS c LEFT JOIN 2_1_journal AS j ON (c.AccountNumber = j.DebitAccount) group by j.DebitAccount";

Do not understand how subtrac one sum from other.

Need to get output like this

Account number is 2310, Value is 21.00 (25+200-4-200)

Account number is 2380, Value is 29.00 (4+200+50-25-200)

Account number is 2620, Value is -50.00 (0(DebitAccount turnover) - 50(CreditAccount turnover))

Query should be the following:

(SUM(Amount) FROM table 2_1_journal WHERE DebitAccount in table 2_1_journal is the same as AccountNumber in table 18_7_ChartOfAccounts)

then

SUM(Amount) FROM table 2_1_journal WHERE CreditAccount in table 2_1_journal is the same as AccountNumber in table 18_7_ChartOfAccounts

then

if DebitAccount is equal to CreditAccount, subtract SUM(Amount)DebitAccount from SUM(Amount)CreditAccount

No idea how to create such query. Please, advice.

Based on advises tried to add condition for select. Here is with modifications. Is the code correct? Tried and appears that works as necessary

SELECT
a.AccountNumber, 
IFNULL( d.Amount, 0 ) - IFNULL( c.Amount, 0 ) AS Amount

FROM 18_7_ChartOfAccounts AS a

LEFT JOIN (
SELECT
    DebitAccount, RecordYear,
    SUM( Amount ) AS Amount
FROM 2_1_journal WHERE RecordYear = 2013
GROUP BY DebitAccount
) d ON (a.AccountNumber = d.DebitAccount)

LEFT JOIN (
SELECT
    CreditAccount, RecordYear,
    SUM( Amount ) AS Amount
FROM 2_1_journal WHERE RecordYear = 2013
GROUP BY CreditAccount
) c ON (a.AccountNumber = c.CreditAccount)

Upvotes: 0

Views: 1346

Answers (2)

walterquez
walterquez

Reputation: 359

SELECT
    a.AccountNumber,
    IFNULL( d.Amount, 0 ) - IFNULL( c.Amount, 0 ) AS Amount

FROM 18_7_ChartOfAccounts AS a

LEFT JOIN (
    SELECT
        DebitAccount,
        SUM( Amount ) AS Amount
    FROM 2_1_journal
    GROUP BY DebitAccount
) d ON a.AccountNumber = d.DebitAccount

LEFT JOIN (
    SELECT
        CreditAccount,
        SUM( Amount ) AS Amount
    FROM 2_1_journal
    GROUP BY CreditAccount
) c ON a.AccountNumber = d.CreditAccount

Upvotes: 6

Kickstart
Kickstart

Reputation: 21533

Something like this should do it (not tested)

SELECT a.ID, a.AccountNumber, IFNULL( CreditAmount, 0 ) - IFNULL( DebitAmount, 0 ) 
FROM 18_7_ChartOfAccounts a
LEFT OUTER JOIN (SELECT DebitAccount AS AccountNo, SUM(Amount) AS DebitAmount FROM 2_1_journal GROUP BY DebitAccount) b ON a.AccountNumber = b.AccountNo
LEFT OUTER JOIN (SELECT CreditAccount AS AccountNo, SUM(Amount) AS CreditAmount FROM 2_1_journal GROUP BY CreditAccount) c ON a.AccountNumber = c.AccountNo
LEFT OUTER JOIN 2_1_journal c ON a.AccountNumber = c.CreditAccount
GROUP BY a.ID, a.AccountNumber

Upvotes: 2

Related Questions