I need to create a MariaDB SQL that will allow me to sum two columns (Debit & Credit Columns) to get the difference, as well as return the subtotal of every different account type. The table is as follows:
Account | Debit | Credit
acc1 | 1 | 2
acc1 | 1 | 4
acc2 | 3 | 2
acc2 | 2 | 1
acc2 | 2 | 1
acc3 | 5 | 2
acc3 | 5 | 1
acc3 | 5 | 2
I would like to return the following:
Account | Balance(debit-credit)
acc1 | -1
acc1 | -3
-------------------------------
Total acc1 | -4
-------------------------------
acc2 | 1
acc2 | 1
acc2 | 1
-------------------------------
Total acc2 | 3
-------------------------------
acc3 | 3
acc3 | 4
acc3 | 3
-------------------------------
Total acc3 | 10
-------------------------------
GrandTotal | 9
-------------------------------
Grandtotal is Totals of acc1 + acc2 + acc3
This is what I have tried so far, how ever all I am only getting the grandtotal and no subtotals
SELECT * FROM (
SELECT COALESCE(account,'TOTAL') AS Account, CASE
WHEN account LIKE 'INC%'
THEN sum((gl.credit - gl.debit))
ELSE sum((gl.debit - gl.credit))
END AS Balance
FROM `tabGL Entry` gl
WHERE (NOT (account LIKE 'CASS%')
AND NOT (account LIKE 'CLIA%')
AND NOT (account LIKE 'FASS%'))
GROUP BY account WITH ROLLUP
) AS gl
ORDER BY CASE
WHEN account LIKE 'INC%' THEN 1
WHEN account LIKE 'DCOI%' THEN 2
WHEN account LIKE 'DMC%' THEN 3
WHEN account LIKE 'INFC%' THEN 4
WHEN account LIKE 'IDEX%' THEN 5
ELSE 6
END