Loading

select count distinct id

JAYRAM

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

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Satyapriya Nayak12y ago
Accepted 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 Trivedi12y ago
hi

try...
select idno,sum(male) as Male,sum(female) as Female from #temp
pivot (COUNT(gender) for gender in ([male], [female])) as p
group by idno
Milstein Munakami12y ago
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;