CODEWITHSUNDEEP
sql
Sung
Sung

Reputation: 209

How do I consolidate the Date column?

I have a query:

SELECT 
    (CONVERT (DATE, mrSUBMITDATE)) as 'Date',
    SUM(CASE WHEN Submission__bTracking = 'Email' THEN 1 ELSE 0 END) as SubmittedbyEmail,
    SUM(CASE WHEN Submission__bTracking = 'Phone' THEN 1 ELSE 0 END) as SubmittedbyPhone,
    SUM(CASE WHEN Submission__bTracking = 'Verbal' THEN 1 ELSE 0 END) as SubmittedbyVerbal,
    SUM(CASE WHEN Submission__bTracking = 'Web' THEN 1 ELSE 0 END) as SubmittedbyWeb
FROM 
    MASTER30
WHERE 
    mrSUBMITDATE >= (CONVERT (DATE, '2015-01-01'))
    AND mrSUBMITDATE < (CONVERT (DATE, '2015-02-01'))
GROUP BY 
    mrSUBMITDATE
ORDER BY 
    mrSUBMITDATE

That produces the following result:

Date    SubmittedbyEmail    SubmittedbyPhone    SubmittedbyVerbal   SubmittedbyWeb
2015-01-01  0                       0                   1               0
2015-01-01  0                       0                   1               0
2015-01-01  1                       0                   0               0
2015-01-01  0                       1                   0               0
2015-01-01  0                       0                   0               1
2015-01-01  0                       0                   1               0
2015-01-01  0                       0                   0               1
2015-01-01  0                       0                   1               0


<<snipped>>

I want to be able to consolidate so that the totals appear for each day, instead of multiple rows for each day shows up. Could someone help me out?

Thanks very much.

Upvotes: 1

Views: 33

Answers (1)

MarcinJuraszek
MarcinJuraszek

Reputation: 125620

Make your GROUP BY use the same CONVERT call as you have in SELECT clause:

SELECT
    (CONVERT (DATE, mrSUBMITDATE)) as 'Date',
    SUM(CASE WHEN Submission__bTracking = 'Email' THEN 1 ELSE 0 END) as SubmittedbyEmail,
    SUM(CASE WHEN Submission__bTracking = 'Phone' THEN 1 ELSE 0 END) as SubmittedbyPhone,
    SUM(CASE WHEN Submission__bTracking = 'Verbal' THEN 1 ELSE 0 END) as SubmittedbyVerbal,
    SUM(CASE WHEN Submission__bTracking = 'Web' THEN 1 ELSE 0 END) as SubmittedbyWeb
FROM MASTER30
WHERE mrSUBMITDATE >= (CONVERT (DATE, '2015-01-01'))
    AND mrSUBMITDATE < (CONVERT (DATE, '2015-02-01'))
GROUP BY (CONVERT (DATE, mrSUBMITDATE))
ORDER BY mrSUBMITDATE

Upvotes: 3

Related Questions