getting the sum of value [Resolved]

Posted by Shoyebaziz123 under Sql Server on 11/26/2011 | Points: 10 | Views : 1240 | Status : [Member] | Replies : 7
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




Responses

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

Up
0
Down

Resolved
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down

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

Login to post response