SQL 分组科目编号并获取小计和总计 [英] SQL Grouping Acct Numbers and Getting Subtotals and Grand Total

问题描述

这个查询...

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屋！