tsql - 如何在新列中显示汇总数据？

Question

我有以下查询返回 android 的数量2011 年 StackOverflow 上每天的问题。我想获得 2011 年提出的所有问题的总和。为此，我正在使用 ROLLUP。

select 
  year(p.CreationDate) as [Year],
  month(p.CreationDate) as [Month],
  day(p.CreationDate) as [Day],
  count(*) as [QuestionsAskedToday]
from Posts p 
  inner join PostTags pt on p.id = pt.postid
  inner join Tags t on t.id = pt.tagid
where 
  t.tagname = 'android' and
  p.CreationDate > '2011-01-01 00:00:00'
group by year(p.CreationDate), month(p.CreationDate),day(p.CreationDate)
​with rollup
order by year(p.CreationDate), month(p.CreationDate) desc,day(p.CreationDate) desc​

这是输出:

2011 年每天提出的所有问题的总和显示在 QuestionsAskedToday 列中。

有没有办法用别名在新列中显示汇总？

Link to the query

Answer 1

要将其显示为列而不是行，您可以使用 SUM(COUNT(*)) OVER () 而不是 ROLLUP。 ( Online Demo )

SELECT YEAR(p.CreationDate)  AS [Year],
       MONTH(p.CreationDate) AS [Month],
       DAY(p.CreationDate)   AS [Day],
       COUNT(*)              AS [QuestionsAskedToday],
       SUM(COUNT(*)) OVER () AS [Total]
FROM   Posts p
       INNER JOIN PostTags pt
         ON p.id = pt.postid
       INNER JOIN Tags t
         ON t.id = pt.tagid
WHERE  t.tagname = 'android'
       AND p.CreationDate > '2011-01-01 00:00:00'
GROUP  BY YEAR(p.CreationDate),
          MONTH(p.CreationDate),
          DAY(p.CreationDate)  
ORDER  BY YEAR(p.CreationDate),
          MONTH(p.CreationDate) DESC,
          DAY(p.CreationDate) DESC