Hello
Below is the schema with sample data of table es_preadmission
I am trying to get the number of male and female students from each categories (GEN, SC,ST,OBC), bpl, sgc, handi,ser_cat class wise. Example
class 1 => GEN => Male = 3, class 1 => GEN => Female = 2
class 1 => BPL => Male = 1, class 1 => BPL => Female = 0
Now to achive the above numbers I am trying something like this -
SELECT pre_scat_id AS cat, pre_gender, COUNT(admno)
FROM es_preadmission
WHERE pre_scat_id IN ('GEN','SC','ST','OBC')
GROUP BY pre_scat_id, pre_gender
UNION ALL
SELECT pre_handi AS cat, pre_gender, COUNT(admno)
FROM es_preadmission
WHERE pre_handi = 'Yes' OR pre_handi = 'No'
GROUP BY pre_handi, pre_gender
UNION ALL
SELECT pre_bpl AS cat, pre_gender, COUNT(admno)
FROM es_preadmission
WHERE pre_bpl = 'Yes' OR pre_bpl ='No'
GROUP BY pre_bpl, pre_gender;
But I am not getting desired output. Below output is drawn on actual database based on above query.
cat pre_gender COUNT(admno)
GEN Female 216
GEN Male 371
OBC Female 30
OBC Male 50
SC Female 36
SC Male 65
ST Male 9
No Female 281
No Male 495
YES Male 1
No Female 268
No Male 467
Yes Female 13
Yes Male 25
I need help on getting my desired output