MySQL的汇总总计行数 [英] MySQL Subtotals of Rows with Grandtotal
问题描述
我需要创建一个MariaDB SQL,该数据库将允许我对两列(借方和贷方列)求和以得出差额,并返回每种不同帐户类型的小计.下表如下:
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
我想返回以下内容:
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
推荐答案
您可以使用UNION
进行尝试,例如:
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
按COALESCE分组似乎是个问题,所以现在由LEFT(account,3)
完成,并且ORDER
语句必须修改为3个字符
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
这篇关于MySQL的汇总总计行数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!