How to add group by clauses based on different conditions?

I have a database like this : The first column is the name of the city. A, B and C are the columns with binary data 1 for yes and 0 for no.

(Database Name – D1)

City         A              B              C
----    ------------    -----------    --------
Boston      1               1              1
Chicago     1               0              0
Boston      1               0              0
Boston      1               1              0
Chicago     1               1              0

I want to group these cities and have columns giving me counts of 1’s in column A, B, and C like below

City       Count A        Count B       Count C     
----    ------------    -----------    --------
Boston      3               2              1
Chicago     2               1              0

The following code will give me the first column. Is there a way I can get all the three columns using a single query by using group by?

SELECT City, COUNT(A) as Count A
FROM D1
GROUP BY City
Where City.A = 1

Answer

If you need to use count (to return a 0 if null values are present) then you can use a case statement within the count function to achieve this.

declare @city table (
    City char(15),
    A int,
    B int,
    C int
);
insert @city (City, A, B, C)
values
('Boston', 1, 1, 1),
('Chicago', 1, 0, 0),
('Boston', 1, 0, 0),
('Boston', 1, 1, 0),
('Chicago', 1, 1, 0);

select * from @city;

select City,
       count(case when A>0 then 1 end) as CountA,
       count(case when B>0 then 1 end) as CountB,
       count(case when C>0 then 1 end) as CountC
from @city group by City;