I’m trying to write a TSQL query that reduces the following query result structure …

Success ZipfileId
------- --------------------
1       10123
1       10123
0       10123
0       10123

Into a single row like the following result structure …

FailureCount SuccessCount ZipfileId
------------ ------------ ---------
2            2            10123

The ZipfileId is the key that I am trying to group by, and I want to create two columns with the counts of the distinct values (0, 1 – it’s a bit column type) in Success. One column for a count of the times 1 appears and another for a count of the time 0 appears.

I’ve tried GROUP BY but can’t seem how to make two columns with different “WHERE” conditions.