CODEWITHSUNDEEP
t-sqlrollup
Animesh D
Animesh D

Reputation: 5002

How to display rollup data in new column?

I have the following query which returns the number of questions per each day on StackOverflow in the year of 2011. I want to get the sum of all the questions asked during the year 2011. For this I am using 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​

This is the output:

output

The sum of all questions asked on each day in 2011 is being displayed in the QuestionsAskedToday column itself.

Is there a way to display the rollup in a new column with an alias?

Link to the query

Upvotes: 3

Views: 1429

Answers (2)

Adam Wenger
Adam Wenger

Reputation: 17560

You could take an approach like this: Example

SELECT 
   YEAR(p.CreationDate) AS 'Year'
   , CASE
        WHEN GROUPING(MONTH(p.CreationDate)) = 0
           THEN CAST(MONTH(p.CreationDate) AS VARCHAR(2))
        ELSE 'Totals:'
     END AS 'Month'
   , CASE
        WHEN GROUPING(DAY(p.CreationDate)) = 0
        THEN CAST(DAY(p.CreationDate) AS VARCHAR(2))
        ELSE 'Totals:'
     END AS [DAY]
   , CASE
        WHEN GROUPING(MONTH(p.CreationDate)) = 0
           AND GROUPING(DAY(p.CreationDate)) = 0 
              THEN COUNT(1)
     END AS 'QuestionsAskedToday'
   , CASE
        WHEN GROUPING(MONTH(p.CreationDate)) = 1
           OR GROUPING(DAY(p.CreationDate)) = 1
              THEN COUNT(1)
     END AS 'Totals'
FROM Posts AS p
INNER JOIN PostTags AS pt ON p.id = pt.postid
INNER JOIN Tags AS t ON t.id = pt.tagid
WHERE t.tagname = 'android'
   AND p.CreationDate >= '2011-01-01'
GROUP BY ROLLUP(YEAR(p.CreationDate)
   , MONTH(p.CreationDate)
   , DAY(p.CreationDate))
ORDER BY YEAR(p.CreationDate)
   , MONTH(p.CreationDate) DESC
   , DAY(p.CreationDate) DESC​​​​​​​

If this is what you wanted, the same technique can be applied to Years as well to total them in the new column, or their own column, if you want to query for multiple years and aggregate them.

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453608

To show this as a column rather than a row you can use SUM(COUNT(*)) OVER () instead of 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

Upvotes: 2

Related Questions