MySQL GROUP certain rows then GROUP BY a column -

MySQL GROUP certain rows then GROUP BY a column -

i have table name myrecord:

stu_name | gender | level ------------------------- john | male | pre-primary jane | female | year 1 sue | female | year 2

i want grouping year 1 , year 2 primary. i've tried next code it's not working.

select sum(gender = 'male') male, sum(gender = 'female') female, ( substr(level, year 1, year 2) primary, substr(level, pre-primary) pre-primary) as level from myrecord group level

the expected result be:

male | female | level ------------------------- 1 | 0 | pre-primary 0 | 2 | primary

is possible accomplish that? tried utilize concat it's not working too. need help!

query select sum(t.male) male, sum(t.female) female, t.level ((select sum(gender='male') male, sum(gender='female') female, case when level='pre-primary' 'pre-primary' when level 'year%' 'primary' else null end level myrecord grouping level) t) grouping t.level; fiddle demo

mysql group rows