SQL query to group by datetime and get total values for 2 other fields

I have a problem writing the sql query that will group a datetime column (Modified) into days, and for each day give me the count of a value of another field, Status which can be either 1 or 2.

I have tried with just getting the count of status=1, but even this doesn’t give my what I want:

SELECT DateValue([a.Modified]) AS Expr1, Count(b.Modified) AS Total
FROM MyTable AS a LEFT JOIN (SELECT Modified FROM MyTable WHERE Status = 1)  AS b ON 
a.Modified = b.Modified
GROUP BY DateValue([a.Modified]);

Below are some sample data and the results I want.

Modified             Status

01-10-2004 20:17:00    1

01-10-2004 20:17:00    2

02-10-2004 11:53:31    1

02-10-2004 11:53:31    2

02-10-2004 11:55:26    2

02-10-2004 11:55:26    1

02-10-2004 12:00:56    2

02-10-2004 12:02:39    2

02-10-2004 12:04:28    1

02-10-2004 12:04:28    2

02-10-2004 12:06:18    2

02-10-2004 12:08:19    1

02-10-2004 12:08:19    2

02-10-2004 12:09:50    2

02-10-2004 12:09:50    1

02-10-2004 12:11:21    2

02-10-2004 12:11:22    1

02-10-2004 12:12:41    2

02-10-2004 12:12:41    1

03-10-2004 09:27:20    1

03-10-2004 09:27:20    2

Expected results:

Modified    Status1Count    Status2Count

02-10-2004     7               10

Answer

This should give you what you are after:

SELECT 
    CAST(Modified AS DATE) AS DateValue, 
    SUM(CASE WHEN [Status] = 1 THEN 1 ELSE 0 END) AS Status1Count,
    SUM(CASE WHEN [Status] = 2 THEN 1 ELSE 0 END) AS Status2Count    
FROM MyTable
GROUP BY 
    CAST(Modified AS DATE)

Advertisement

Answer