Query multiple aggregations in a single row

Question

I want a query that returns results from 3 months, 6 months and 12 months in their own column.

3 | 6 | 12   
V1| V2| V3

I have a query that will give me any one of these results:

Code to get one value:

SELECT SUM(CONVERT(bigint,R.FileSize)) / (1024*1024) AS '3' 
FROM Revisions R
JOIN Documents D ON D.DocumentID = R.DocumentID
WHERE R.Date BETWEEN dateadd(month, -3, GETDATE()) AND getdate()

Results:
3
205

I tried to embed the SQL query and combine them.

SELECT b.3, SUM(CONVERT(bigint,R.FileSize)) / (1024*1024) AS '6'
FROM Revisions R2
JOIN Documents D2 ON D2.DocumentID = R2.DocumentID
FULL OUTER JOIN b ON b.3 = 6

(SELECT SUM(CONVERT(bigint,R.FileSize)) / (1024*1024) AS '3' 
 FROM Revisions R
 JOIN Documents D ON D.DocumentID = R.DocumentID
 WHERE R.Date BETWEEN dateadd(month, -3, GETDATE()) AND getdate()) b
WHERE R2.Date BETWEEN dateadd(month, -6, GETDATE()) AND getdate();

This question was about MySQL, but the same answer also works in SQL Server: https://stackoverflow.com/questions/12789396/how-to-get-multiple-counts-with-one-sql-query — Tab Alleman, Feb 12 '19 at 18:11

score 1 · Answer 1 · answered Feb 12 '19 at 18:14

I think you want conditional aggregation:

SELECT SUM(CASE WHEN R.Date BETWEEN dateadd(month, -3, GETDATE()) AND getdate()
                THEN CONVERT(bigint, R.FileSize)) / (1024*1024)
           END) AS filesize_3,
       SUM(CASE WHEN R.Date BETWEEN dateadd(month, -6, GETDATE()) AND getdate()
                THEN CONVERT(bigint, R.FileSize)) / (1024*1024)
           END) AS filesize_6,
       SUM(CASE WHEN R.Date BETWEEN dateadd(month, -9, GETDATE()) AND getdate()
                THEN CONVERT(bigint, R.FileSize)) / (1024*1024)
           END) AS filesize_9        
FROM Revisions R JOIN
     Documents D
     ON D.DocumentID = R.DocumentID

Query multiple aggregations in a single row

1 Answers1