select count distinct id

Question

i have table like this

sno name dno gender idno

1 shashi 5-4 male 123
2 roja 6-4 female 123
3 raju 7-4 male 134
4 pravalika 8-9 female 134
5 vamshi 9-2 male 123

i want count male and female group by idno

i wnat output like this

idno male female
123 2 1
134 1 1

Satyapriya Nayak · Answer

SELECT SUM(CASE WHEN gender = 'male' THEN 1 ELSE 0 END) as MaleCount SUM(CASE WHEN gender = 'female' THEN 1 ELSE 0 END) as FemaleCount idno FROM tab3 group by idno

Jignesh Trivedi · Answer

hi try... select idnosum(male) as Malesum(female) as Female from #temp pivot (COUNT(gender) for gender in ([male] [female])) as p group by idno

Milstein Munakami · Answer

Best way to do solve such problem is use PIVOT Solution: WITH a AS( SELECT idno gender FROM tbl_users ) SELECT * FROM a pivot (COUNT(gender) for [gender] in ([male] [female])) as p;