November 11, 2011 at 3:20 am
I have a table like:
Name District Gender
R1 Dhaka Male
R2 Dhaka Male
R3 Pabna Male
R4 Pabna Female
R5 Pabna Female
R6 Dhaka Female
R7 Khulna Female
. . .
. . .
. . .
I want output like:
District Male Female
Dhaka 2 1
Pabna 1 2
Khulna 0 1
. . .
. . .
What will be the SQL Query
November 11, 2011 at 3:30 am
Read up on Count and Group By.
Hint: Start with this
SELECT District, CASE Gender WHEN 'Male' THEN 1 ELSE 0 END as Male, CASE Gender WHEN 'Female' THEN 1 ELSE 0 END as Female
From ATable
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 11, 2011 at 4:14 am
I had to use sum instead of count
Thanks a lot GilaMonster 🙂
November 14, 2011 at 12:53 am
select distinct a.district,c.male as male,b.female as female from testtable a
left join
(select district,count(gender) as female from testtablec
group by district,gender
having gender='female'
)b
on b.district=a.district
left join
(select district,count(gender) as male from testtablec
group by district,gender
having gender='male'
)c
on c.district=a.district
November 14, 2011 at 12:54 am
select distinct a.district,c.male as male,b.female as female from testtable a
left join
(select district,count(gender) as female from testtable
group by district,gender
having gender='female'
)b
on b.district=a.district
left join
(select district,count(gender) as male from testtable
group by district,gender
having gender='male'
)c
on c.district=a.district
November 14, 2011 at 12:56 am
change table name as what you have given i have used testtable
November 14, 2011 at 2:16 am
That's an immensely complex and less efficient way of doing this.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply