CODEWITHSUNDEEP
sqlsql-servergroup-by
Sung
Sung

Reputation: 209

How do I GROUP the results of a query that is already grouped?

I have a query:

SELECT

CONVERT(varchar(7),SUBMITDATE, 120) as 'Month'

,CASE WHEN ReportType = '1' THEN (SELECT AVG(DATEDIFF(DAY,SUBMITDATE,DateClosed))) END as 'Report1Avg'
,CASE WHEN ReportType = '2' THEN (SELECT AVG(DATEDIFF(DAY,SUBMITDATE,DateClosed))) END as 'Report2Avg'
,CASE WHEN ReportType = '3' THEN (SELECT AVG(DATEDIFF(DAY,SUBMITDATE,DateClosed))) END as 'Report3Avg'
,CASE WHEN ReportType = '4' THEN (SELECT AVG(DATEDIFF(DAY,SUBMITDATE,DateClosed))) END as 'Report4Avg'
,CASE WHEN ReportType = '5' THEN (SELECT AVG(DATEDIFF(DAY,SUBMITDATE,DateClosed))) END as 'Report5Avg'

FROM Table1

    WHERE STATUS = 'Closed'

GROUP BY CONVERT(varchar(7),SUBMITDATE, 120), ReportType
ORDER BY CONVERT(varchar(7),SUBMITDATE, 120)

Which produces the following result:

query results

My question is: How do I consolidate the results of each month in one row? ex. for '2015-06', I have 3 rows of results.

Is this possible?

Upvotes: 0

Views: 37

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Use avg around the case expression. Also use else 0 to avoid null values.

SELECT
CONVERT(varchar(7),SUBMITDATE, 120) as 'Month'
,AVG(CASE WHEN ReportType = '1' THEN DATEDIFF(DAY,SUBMITDATE,DateClosed) ELSE 0 END) as 'Report1Avg'
,AVG(CASE WHEN ReportType = '2' THEN DATEDIFF(DAY,SUBMITDATE,DateClosed) ELSE 0 END) as 'Report2Avg'
,AVG(CASE WHEN ReportType = '3' THEN DATEDIFF(DAY,SUBMITDATE,DateClosed) ELSE 0 END) as 'Report3Avg'
,AVG(CASE WHEN ReportType = '4' THEN DATEDIFF(DAY,SUBMITDATE,DateClosed) ELSE 0 END) as 'Report4Avg'
,AVG(CASE WHEN ReportType = '5' THEN DATEDIFF(DAY,SUBMITDATE,DateClosed) ELSE 0 END) as 'Report5Avg'
FROM Table1
WHERE STATUS = 'Closed'
GROUP BY CONVERT(varchar(7),SUBMITDATE, 120)
ORDER BY CONVERT(varchar(7),SUBMITDATE, 120)

Upvotes: 1

Related Questions