SELECT YEAR(p.CreationDate) AS 'Year' , CASE WHEN GROUPIN...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for programmers

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

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results
:records returned in :time ms:cached