SQL: Displaying counts in a column for diff values in same row-CodePudding

I have a table 'patients' which has a column 'gender'. I want to show number of males and females in 2 columns side-by-side. I do it as:

Select * from 
(SELECT count(gender) AS male_count from patients
GROUP BY gender
having gender = 'M')
CROSS JOIN
(SELECT count(gender) as female_count from patients
GROUP BY gender
having gender = 'F')

It works, but I am doing SELECT two times. I guess there is a better way of achieving this.

CodePudding user response：

An aggregated conditional case expression should be all you need

select 
    Sum(case when gender='M' then 1 end) Male_Count,
    Sum(case when gender='F' then 1 end) Female_Count
from patients;