0

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).

1

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';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.