Filter 1 columns data into 3 columns


enter image description here

I need to filter data in 1 column and have an output of three columns (Column A,Column B and Column C).

The output columns should display values of 11 for column A, values of 12 for column B, and Total of A & B for column C.

The current query I have written:

select count(zcsms_studentgender) as MaleStudents, 
       count(zcsms_studentgender) as FemaleStudents, 
       count(zcsms_studentgender) as TotalStudents
from zcsms_studentdefinition 
where (zcsms_studentgender = '11' or zcsms_studentgender = '12') 
and zcsms_studentactive = 'Y';

I can't seem to add filtering into the first two columns (MaleStudents & FemaleStudents) to display only count values of 11 (male) and 12 (female).

It's a report based on student gender. The value for male students is 11, the value for female student is 12. These are stored in 1 table, 1 column. My output has to filter these into a report that will display as Column A (Male Students count) Column B (Female Students count) and Column C
(total count of A and B).

Best Answer

Just use SUM() and a CASE statement:

SELECT sum(case when zcsms_studentgender=11 then 1 else 0 end) as malestudents,
       sum(case when zcsms_studentgender=12 then 1 else 0 end) as femalestudents,
       sum(case when zcsms_studentgender in (11,12) then 1 else 0 end) as totalstudents
FROM zcsms_studentdefinition
WHERE zcsms_studentactive = 'Y';