汇总和合计

Question

SELECT  Region ,
        flag ,
        Name,
        COUNT(ID) AS 'CountWithFlag'
FROM    Table
GROUP BY flag

这个查询提供了以下结果。我是按标志分组的，我能够根据标志得到英语/非英语的计数。我还希望在计数旁边显示英语和非英语的总计计数。

产出：

Region Flag  Name     CountWithFlag
a      0     English        100
b      1     Non-English    200 
c      0     English        100
d      1     Non-English    200

期望产出：

Region Flag  Name     CountWithFlag  Total
a      0     English        100       200
b      1     Non-English    200       400
c      0     English        100       200
d      1     Non-English    200       400

我怎么能这么做？我想申请组的具体计数与标志。但是我也想在同一个查询中得到总数！

关于我怎么做到的有什么建议吗？

Answer 1

另一种方式是这样的：

;
WITH    agg1
            AS (
                SELECT  region,
                        flag,
                        name,
                        COUNT(id) AS 'CountWithFlag'
                FROM    [dbo].[t2]
                GROUP BY region,
                        flag,
                        name
                ),
        agg2
            AS (
                SELECT  [name],
                        COUNT(id) AS CountByName
                FROM    [dbo].[t2]
                GROUP BY [name]
                )
    SELECT  [agg1].[region],
            [agg1].[flag],
            [agg1].[name],
            [agg1].[CountWithFlag],
            [agg2].[CountByName]
    FROM    [agg1]
            INNER JOIN [agg2]
                ON [agg2].[name] = [agg1].[name]

Answer 2

尝尝这个

  ;
    WITH    cte
              AS ( SELECT DISTINCT
                            Region ,
                            flag ,
                            Name ,
                            COUNT(ID) OVER ( PARTITION BY flag, Region, Name ) AS [CountWithFlag]
                   FROM     [Table]
                 )
        SELECT  Region ,
                flag ,
                Name ,
                SUM([CountWithFlag]) OVER ( PARTITION BY Name ) AS Total
        FROM    cte

Answer 3

如果要避免使用窗口函数，可以这样做：

SELECT 
  Region,
  flag,
  Name,
  COUNT(ID) AS CountWithFlag,
  (select count(ID) from Table as tbl1 where tbl1.Name=tbl.Name) AS Total
from Table as tbl
group by Region, flag, Name

但我的观点是，窗口聚合应该运行得更快。

如果要使用窗口聚合，请执行以下操作：

select 
  Region,
  flag,
  Name,
  CountWithFlag,
  sum(CountWithFlag) over(partition by Name) as Total
from (
  SELECT 
    Region,
    flag,
    Name,
    COUNT(ID) AS CountWithFlag
  from Table as tbl
  group by Region, flag, Name
) as tbl