Member Avatar for vishalonne

Hello
Below is the schema with sample data of table es_preadmission

b3b82faffc743e08d6d6329b3a7b956d

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

  • 4 Contributors
  • 5 Replies
  • 581 Views
  • 1 Month Discussion Span
  • Latest Post Latest Post by joshmac

Dani AI

Generated

Brief diagnosis and recommended approach

The repeated "No" rows come from treating the Yes/No columns as separate categories. In the UNION you included both "Yes" and "No" values, so the same literal "No" appears once for pre_handi and again for pre_bpl. That produces multiple "No" groups and makes the output confusing. Normalize the data first: map boolean columns to explicit category names only when they mean something (e.g. map pre_handi='Yes' to "PH"), and exclude the raw "No" values. Also be explicit about whether categories are exclusive — a student can legitimately appear in more than one category (GEN + BPL + PH) unless exclusivity is enforced.

Normalized-count query (tall format: category, class, M/F)

SELECT cat, pre_class,
       SUM(pre_gender = 'Male')   AS male_count,
       SUM(pre_gender = 'Female') AS female_count
FROM (
  SELECT admno, pre_class, pre_gender, pre_scat_id AS cat
    FROM es_preadmission
   WHERE pre_scat_id IN ('GEN','SC','ST','OBC')
  UNION ALL
  SELECT admno, pre_class, pre_gender, 'PH'  AS cat
    FROM es_preadmission
   WHERE pre_handi = 'Yes'
  UNION ALL
  SELECT admno, pre_class, pre_gender, 'BPL' AS cat
    FROM es_preadmission
   WHERE pre_bpl  = 'Yes'
) AS t
GROUP BY cat, pre_class
ORDER BY cat, pre_class;

Practical notes and next steps

  • To get the exact matrix shown in post #3 (class columns with M/F subcolumns), either pivot the above with conditional aggregation for each class (fixed set) or generate a dynamic pivot (or build the table in PHP from the tall result).
  • Standardize values first (TRIM/UPPER on pre_gender, check NULL vs empty strings) to avoid miscounts.
  • If counts must be exclusive (a single category per student), add logic to decide priority (e.g. caste first, then PH/BPL as flags) before aggregating.
  • Posting a minimal SQL dump (as and suggested) makes it easier to test edge cases; thanks to for creating a test fiddle earlier.

Recommended Answers

All 5 Replies

Member Avatar for Member #120589

An SQL dump would be most useful. :)

Member Avatar for 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
Member Avatar for Member #120589

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

Member Avatar for 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.