Is it possible to combine these two sql statements into one statement using group by?-CodePudding

select client_type, count(gender) as num_males
from clients
where gender = 'Male'
group by client_type;

select client_type, count(gender) as num_females
from clients
where gender = 'Female'
group by client_type;

The following SQL statements show the number of males by client type, then the number of females by client type. I would like an SQL statement to show the following columns: client_type, count(gender = 'Male'), count(gender = 'Female'). Is it possible to do this?

CodePudding user response：

You could count a couple of case expressions:

SELECT   client_type, 
         COUNT(CASE gender WHEN 'Male' THEN 1 END) AS num_males,
         COUNT(CASE gender WHEN 'Female' THEN 1 END) AS num_females
FROM     clients
GROUP BY client_type;