Get number of students class wise

[vishalonne]

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

An SQL dump would be most useful. :)

[vishalonne]

SQL Dump ??? I need the figures for report generation purpose using php
which will look this this

        1       2 
      M   F   M   F
GEN   20  9   25  10
SC    5   2   9   4
ST    0   3   0   0
OBC   1   2   1   0
PH    0   0   1   0
BPL   2   3   7   2

So give an sql dump of your table, so we can create our own version and test.

[Alberto Bucur]

In translation: make an export of your table in a sql file.
This file must contain a CREATE TABLE statment and some INSERT statment.
Copy and past the content of this file here.
With this sql code we can create our test table.

[joshmac]

Just in case the OP comes back to this, I created it at SQLfiddle: http://sqlfiddle.com/#!2/d6b123/2