MySQL select some values (not all values) from column of one table and depending on the selected values sum values in other table

Question

First table (named 18_8_ChartOfAccounts) with account numbers like this

AccountNumber | VatReturnRowNumberForDebitTurnover
--------------------------------------------------
 1            |    not_blank
 2            |
 3            |    not_blank

Second table (named 2_1_journal) like this

Amount | DebitAccount
----------------------
5     |  1
80    |  2
3     |  3
4     |  1
20    |  3

Trying to get following: if VatReturnRowNumberForDebitTurnover is not empty/not blank, sum Amount where DebitAccount is the same as (equal to) AccountNumber.

Or want to get output like this:

Account number | Total
---------------------------
1              | 9 (5+4)
3              | 23 (20+3)

Do not need to echo Total for account 2, because for account 2 VatReturnRowNumberForDebitTurnover is blank/empty

This mysql query selects all AccountNumbers from first table (18_8_ChartOfAccounts)

SELECT a.AccountNumber, IFNULL( d.Amount, 0 ) AS Amount 
FROM 18_8_ChartOfAccounts AS a 
LEFT JOIN ( SELECT DebitAccount, SUM( Amount ) AS Amount 
FROM ( SELECT * FROM 2_1_journal) DATA 
GROUP BY DebitAccount ) 
d ON (a.AccountNumber = d.DebitAccount)

But need to select only AccountNumbers where VatReturnRowNumberForDebitTurnover is not blank/empty

Trying to modify mysql query

SELECT a.AccountNumber, IFNULL( d.Amount, 0 ) AS Amount 
FROM 18_8_ChartOfAccounts AS a 

WHERE VatReturnRowNumberForDebitTurnover <> ''

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

Get Syntax error or access violation: ... near 'LEFT JOIN ( SELECT DebitAccount ....

Then changed FROM 18_8_ChartOfAccounts AS a WHERE VatReturnRowNumberForDebitTurnover <> ''

to FROM 18_8_ChartOfAccounts WHERE VatReturnRowNumberForDebitTurnover <> '' AS a and get similar error.

Then changed to such code:

SELECT a.AccountNumber, IFNULL( d.Amount, 0 ) AS Amount 
FROM 18_8_ChartOfAccounts AS a 
LEFT JOIN ( SELECT DebitAccount, SUM( Amount ) AS Amount 
FROM ( SELECT * FROM 2_1_journal) DATA 
WHERE a.VatReturnRowNumberForDebitTurnover <> "" 
GROUP BY DebitAccount ) 
d ON (a.AccountNumber = d.DebitAccount)

and get Unknown column 'a.VatReturnRowNumberForDebitTurnover'

Ok in table 2_1_journal no such column. Want to use a.VatReturnRowNumberForDebitTurnover in the same way as a.AccountNumber (both columns are only in 18_8_ChartOfAccounts. a.AccountNumber works, a.VatReturnRowNumberForDebitTurnover not.

Please, advice what need to modify

Answer 1

This should work;

SELECT AccountNumber, 
       CONCAT(SUM(Amount), ' (', GROUP_CONCAT(Amount SEPARATOR '+'), ')') Total
FROM 18_8_ChartOfAccounts
LEFT JOIN 2_1_journal
  ON AccountNumber = DebitAccount
WHERE VatReturnRowNumberForDebitTurnover IS NOT NULL
GROUP BY AccountNumber

An SQLfiddle to test with.

Of course, if the sum details was just an example, you can skip the GROUP_CONCAT() part and just use...

SELECT AccountNumber, COALESCE(SUM(Amount), 0) Total
...

Another SQLfiddle.

Answer 2

Simply Use this query:

SELECT a.AccountNumber, SUM( b.Amount ) AS Amount
  FROM 18_8_ChartOfAccounts AS a 
     LEFT JOIN 2_1_journal AS b ON a.AccountNumber = b.DebitAccount
        WHERE a.VatReturnRowNumberForDebitTurnover IS NOT NULL
        GROUP BY b.DebitAccount;