SQL 分组科目编号并获取小计和总计 [英] SQL Grouping Acct Numbers and Getting Subtotals and Grand Total
本文介绍了SQL 分组科目编号并获取小计和总计的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
这个查询...
SELECT ACTNO,SUM(PLAN) AS 'TOTAL PLAN', SUM(FORECAST) AS 'TOTAL FORECAST'
FROM COST_CENTER
GROUP BY ACTNO
我得到以下结果:
ACTNO | TOTAL PLAN | TOTAL FORECAST
100 | 12000 | 20000
101 | 12000 | 20000
200 | 1000 | 1500
201 | 1200 | 2000
202 | 900 | 1500
203 | 700 | 1500
220 | 1500 | 3500
300 | 1200 | 2000
301 | 1000 | 3000
我需要将某些帐户组合在一起,获取这些组的小计,然后将所有小计加起来作为总计.
I need to group together certain accounts, get those groups' subtotals, and then add all the subtotals up for a grand total.
想要的结果类似于:
ACTNO | TOTAL PLAN | TOTAL FORECAST
100 | 12000 | 20000
101 | 12000 | 20000
24000 40000
200 | 1000 | 1500
201 | 1200 | 2000
202 | 900 | 1500
203 | 700 | 1500
3800 6500
220 | 1500 | 3500
300 | 1200 | 2000
301 | 1000 | 3000
3700 8500
Total 31500 55000
这让我有点接近,但不是我想要的格式.
This gets me somewhat close, but not quite in the format I'd like.
SELECT ACTNO, SUM(PLAN), SUM(FORECAST)
FROM COST_CENTER
WHERE (ACTNO IN ('100','101'))
GROUP BY ACTNO WITH ROLLUP
...
ACTNO | |
NULL | 24000 | 40000
100 | 12000 | 20000
101 | 12000 | 20000
感谢您的时间和帮助.
推荐答案
您可以通过分组集获得更多控制权:
You have more control with grouping sets:
SELECT ACTNO, grp, SUM(PLAN), SUM(FORECAST)
FROM (SELECT cc.*,
(CASE WHEN ACTNO IN ('100', '101') THEN 1
WHEN ACTNO IN ('200', '201', '202', '203') THEN 2
WHEN ACTNO IN ('220', '300', '301') THEN 3
END) as grp
FROM COST_CENTER cc
) cc
GROUP BY GROUPING SETS ( (ACTNO, grp), (grp), ());
您可能可以从 SELECT
中删除 grp
...不过,我从来没有在不包含列的情况下使用 GROUPING SETS
.
You can probably remove grp
from the SELECT
. . . I have never used GROUPING SETS
without including the column, though.
这篇关于SQL 分组科目编号并获取小计和总计的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文