mysql 总计,MySQL的汇总总计行

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

解决方案

You could try it with UNION, like:

SELECT *

FROM (SELECT COALESCE('TOTAL ', account) AS Account

, SUM(CASE WHEN account LIKE 'INC%'

THEN (gl.credit - gl.debit)

ELSE (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

-- UNION SELECT account

-- , CASE WHEN account LIKE 'INC%'

-- THEN (gl.credit - gl.debit)

-- ELSE (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%'))

) 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

I think this should do what you want

The commented-out part is for the single rows, the upper part for subtotals

Now after the last comment here the query that gives you the subtotals and the grandtotal

grouping by COALESCE seemed to be the Problem so now it's done by LEFT(account,3) and the ORDER Statement had to be modified to 3 Chars

SELECT *

FROM (SELECT LEFT(account,3) AS Account

, SUM(CASE WHEN account LIKE 'INC%'

THEN (credit - debit)

ELSE (debit - credit)

END) AS Balance

FROM acc

GROUP BY LEFT(account,3) WITH ROLLUP

) AS ac

ORDER BY CASE

WHEN account LIKE 'INC%' THEN 1

WHEN account LIKE 'DCO%' THEN 2

WHEN account LIKE 'DMC%' THEN 3

WHEN account LIKE 'INF%' THEN 4

WHEN account LIKE 'IDE%' THEN 5

ELSE 6

END