getting the sum of value [Resolved]

Posted by Shoyebaziz123 under Sql Server on 11/26/2011 | Points: 10 | Views : 2241 | Status : [Member] | Replies : 7

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi All,
I have a Table like This....Emp

Id comName Gender
1 a m
2 b m
3 b f
4 b m
5 b m
6 c m
7 e f

now how can i write a query that count total no. of male and female candidate in company ?

o/p (eg)
compName Male Female
b 3 1

Please Help me

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Muhsinathk on: 6/20/2012 [Member] Bronze | Points: 50

0	Mark as Answer if its helpful to you.. Shoyebaziz123, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Arefin on: 11/26/2011 [Member] Starter | Points: 25

0	Hi Shoyebaziz123, Use following query: SELECT CompanyName, Count(CASE Gender When 'm' Then 1 End) Male, Count(CASE Gender When 'f' Then 1 End) Female FROM [TestDB].[dbo].[Employee] Group By CompanyName; I think, it will be helpful for your purpose. Thanks, Arefin Shoyebaziz123, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: PandianS on: 11/27/2011 [Member] [MVP] Silver | Points: 25

0	Hi Can you pls try this ? Select comName Company ,M Male, F Female From ( Select comName,Gender from Emp ) P PIVOT ( COUNT(Gender) FOR Gender IN (M,F))AS PIV Cheers www.SQLServerbuddy.blogspot.com iLink Multitech Solutions Shoyebaziz123, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Shoyeb on: 11/27/2011 [Member] Starter | Points: 25

0	thanks a lot your query work best Shoyebaziz123, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Niladri.biswas on: 11/28/2011 [Member] Platinum | Points: 25

0	Declare @t table(Id int identity,ComName varchar(50),Gender Varchar(1)) Insert Into @t Select 'a','m' Union All Select 'b','m' Union All Select 'b','f' Union All Select 'b','m' Union All Select 'b','m' Union All Select 'c','m' Union All Select 'e','f' Select * From @t Select ComName = 'b' ,Male = (Select COUNT(Gender) From @t Where Gender = 'm' And ComName = 'b') ,FeMale = (Select COUNT(Gender) From @t Where Gender = 'f' And ComName = 'b') Best Regards, Niladri Biswas Shoyebaziz123, if this helps please login to Mark As Answer. \| Alert Moderator

Declare @t table(Id int identity,ComName varchar(50),Gender Varchar(1))
Insert Into @t

Select 'a','m' Union All
Select 'b','m' Union All
Select 'b','f' Union All
Select 'b','m' Union All
Select 'b','m' Union All
Select 'c','m' Union All
Select 'e','f'

Select *
From @t

Select
ComName = 'b'
,Male = (Select COUNT(Gender) From @t Where Gender = 'm' And ComName = 'b')
,FeMale = (Select COUNT(Gender) From @t Where Gender = 'f' And ComName = 'b')

Best Regards,
Niladri Biswas

Shoyebaziz123, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: perfectchourasia-9163 on: 11/28/2011 [Member] Starter | Points: 25

0	SELECT COUNT(case gender when 'm' then 1 end) AS male ,count(case gender when 'f' then 1 end) as female FROM emp ER sandeep chourasia sandeepchrs@yahoo.com (on facebook) http://www.aspnetcodes.com/ Shoyebaziz123, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Muhsinathk on: 6/20/2012 [Member] Bronze | Points: 25

0	SELECT COMNAME,(SELECT COUNT(GENDER)FROM EMP WHERE GENDER='M' AND COMNAME='B') AS MALE,(SELECT COUNT(GENDER)FROM EMP WHERE GENDER='F' AND COMNAME='B') AS FEMALE FROM EMP WHERE COMNAME='B' GROUP BY COMNAME Shoyebaziz123, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts