MySQL Subtotals of Rows with Grandtotal

Viewed 598

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
1 Answers
Related