sql - 使用分组 SQL 计算总计

Question

我有一张 table 。 AVG_QTY_Total 中的每个小计与每个 MMODEL 中使用的组件 Material 的数量相同。但是，对于总计，我想计算 3 个模型的每个小计。那么如果我手动求和的话，应该是240+500+1200 = 1940，怎么算出来的呢？

下面是我目前的查询、结果和期望结果。

当前查询:

  select YYYYMM,(case when GROUPING(MODEL) = 0 and
                  GROUPING(MODEL_DESC) = 1 and 
                  GROUPING(MATERIAL) = 1 
             then 'Total ' + MODEL
             when GROUPING(MODEL) = 1 and
                  GROUPING(MODEL_DESC) = 1 and
                  GROUPING(MATERIAL) = 1 then 'Grand Total'
             else MODEL
        end) as MMODEL,
       MODEL_DESC, MATERIAL,
       sum(AVG_QTY)/COUNT(MODEL) as AVG_QTY_Total,
       sum(PACK_QTY) as PACK_QTY_Total,
       sum(PACK_AMT) as PACK_AMT_Total 
From [SQLDB1].[dbo].[PACKING] 
group by grouping sets((YYYYMM,MODEL, MODEL_DESC ,MATERIAL),
                       (MODEL),
                       ()
                      );

当前结果:

YYYYMM  MMODEL  MODEL_DESC  MATERIAL    AVG_QTY_Total   PACK_QTY_Total  PACK_AMT_Total
201801  DE8     DE80001      A          240             20              3400
201801  DE8     DE80001      B          240             5               600
201801  DE8     DE80001      C          240             60              5000
NULL    Total DE8   NULL    NULL        240             85              9000
201801  DE8B    DE8B002      A          500             45              7600
201801  DE8B    DE8B002      B          500             10              1200
201801  DE8B    DE8B002      C          500             125             11000
NULL    Total DE8B  NULL    NULL        500             180             19800
201801  SF93    SF93005      A          1200            50              10200
201801  SF93    SF93005      B          1200            25              15000
201801  SF93    SF93005      C          1200            300             24000
NULL    Total SF93  NULL    NULL        1200            375             49200
NULL    Grand Total NULL    NULL        646             640             78000

期望结果:

YYYYMM  MMODEL  MODEL_DESC  MATERIAL    AVG_QTY_Total   PACK_QTY_Total  PACK_AMT_Total
201801  DE8     DE80001      A          240             20              3400
201801  DE8     DE80001      B          240             5               600
201801  DE8     DE80001      C          240             60              5000
NULL    Total DE8   NULL    NULL        240             85              9000
201801  DE8B    DE8B002      A          500             45              7600
201801  DE8B    DE8B002      B          500             10              1200
201801  DE8B    DE8B002      C          500             125             11000
NULL    Total DE8B  NULL    NULL        500             180             19800
201801  SF93    SF93005      A          1200            50              10200
201801  SF93    SF93005      B          1200            25              15000
201801  SF93    SF93005      C          1200            300             24000
NULL    Total SF93  NULL    NULL        1200            375             49200
NULL    Grand Total NULL    NULL        1940            640             78000

更新的解决方案 1:

根据 Gordon Linoff 的回答，我设法将结果显示为预期答案。我也会尝试其他人的回答。

with t as (  select YYYYMM,(case when GROUPING(MODEL) = 0 and
                  GROUPING(MODEL_DESC) = 1 and 
                  GROUPING(MATERIAL) = 1 
             then MODEL
             when GROUPING(MODEL) = 1 and
                  GROUPING(MODEL_DESC) = 1 and
                  GROUPING(MATERIAL) = 1 then 'Grand Total'
             else MODEL
        end) as MMODEL,
       MODEL_DESC, MATERIAL,
       avg_qty as AVG_QTY_TT,
       sum(AVG_QTY)/COUNT(MODEL) as AVG_QTY_Total,
       sum(PACK_QTY) as PACK_QTY_Total,
       sum(PACK_AMT) as PACK_AMT_Total 
From [SQLDB1].[dbo].[PACKING] 
group by grouping sets((YYYYMM,MODEL, MODEL_DESC ,MATERIAL,avg_qty),
                       (MODEL),
                       ()
                      )),
     m as (
      select  Sum(  AVG_QTY_TT)/ COUNT(DISTINCT MODEL_DESC ) AS cnt
      from t

     )
select YYYYMM,MMODEL,MODEL_DESC,MATERIAL,
       (case when mmodel = 'Grand Total' then  (select cnt from m)
             else avg_qty_total
        end) as AVG_QTY_Total,
        PACK_QTY_Total,PACK_AMT_Total
from t;

Answer 1

您不能为此真正使用分组集。 . .至少就其本身而言。

让我试着说服您，您不想要 1,940。这是三个不同组的平均值之和。在每个组中，您正在计算平均值。那么为什么在组合它们时对它们求和呢？

您可以尝试在事后调整这些值。本质上，运行您拥有的查询，并执行如下操作:

with t as (<your query here>),
     m as (
      select count(distinct model) cnt
      from t
      where yyyymm is null
     )
select . . .,
       (case when mmodel = 'Grand Total' then AVG_QTY_Total * (select cnt from m)
             else avg_qty_total
        end)
from t;

关于其他警告。由于整数的舍入问题，这将产生 1938。因此，您还需要修复算法以使用小数/ float 而不是整数。