[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

male vs female

rodchar

3/15/2007 3:15:00 PM

hey all,
i have an employee table of the following:
empID, Gender, Salary

how do i get a count of male vs female?

also, is it possible, in addition to the above, get the percentage of male
and female, and avg of their salary all in one statment?

thanks,
rodchar
5 Answers

Tom Cooper

3/15/2007 3:42:00 PM

0

Answers inline
"rodchar" <rodchar@discussions.microsoft.com> wrote in message
news:596BBA47-8FE0-4966-AD7A-2ACB210A8892@microsoft.com...
> hey all,
> i have an employee table of the following:
> empID, Gender, Salary
>
> how do i get a count of male vs female?

Select e.Gender, Count(*) As Number
From employees e
Group By e.Gender
Order By e.Gender

>
> also, is it possible, in addition to the above, get the percentage of male
> and female, and avg of their salary all in one statment?

Select e.Gender, Count(*) As Number,
100 * (Count(*) / t.TotalCount) As Percent,
Avg(e.Salary) As AverageSalary
From employees e
Cross Join (Select Count(*) As TotalCount
From employees e1) As t
Group By e.Gender
Order By e.Gender

>
> thanks,
> rodchar

Tom


rodchar

3/15/2007 4:26:00 PM

0

Hey Tom,
I'm getting the following error:
Column 't.TotalCount' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.

"Tom Cooper" wrote:

> Answers inline
> "rodchar" <rodchar@discussions.microsoft.com> wrote in message
> news:596BBA47-8FE0-4966-AD7A-2ACB210A8892@microsoft.com...
> > hey all,
> > i have an employee table of the following:
> > empID, Gender, Salary
> >
> > how do i get a count of male vs female?
>
> Select e.Gender, Count(*) As Number
> From employees e
> Group By e.Gender
> Order By e.Gender
>
> >
> > also, is it possible, in addition to the above, get the percentage of male
> > and female, and avg of their salary all in one statment?
>
> Select e.Gender, Count(*) As Number,
> 100 * (Count(*) / t.TotalCount) As Percent,
> Avg(e.Salary) As AverageSalary
> From employees e
> Cross Join (Select Count(*) As TotalCount
> From employees e1) As t
> Group By e.Gender
> Order By e.Gender
>
> >
> > thanks,
> > rodchar
>
> Tom
>
>
>

Tom Cooper

3/15/2007 4:59:00 PM

0

Sorry, try

Select e.Gender, Count(*) As Number,
100 * (Cast(Count(*) As decimal(19,5))/ Max(t.TotalCount)) As 'Percent',
Avg(e.Salary) As AverageSalary
From employees e
Cross Join (Select Count(*) As TotalCount
From employees e1) As t
Group By e.Gender
Order By e.Gender

Tom

"rodchar" <rodchar@discussions.microsoft.com> wrote in message
news:01DA8DFB-BF21-47DD-B1D1-986E5ECFBCA6@microsoft.com...
> Hey Tom,
> I'm getting the following error:
> Column 't.TotalCount' is invalid in the select list because it is not
> contained in either an aggregate function or the GROUP BY clause.
>
> "Tom Cooper" wrote:
>
>> Answers inline
>> "rodchar" <rodchar@discussions.microsoft.com> wrote in message
>> news:596BBA47-8FE0-4966-AD7A-2ACB210A8892@microsoft.com...
>> > hey all,
>> > i have an employee table of the following:
>> > empID, Gender, Salary
>> >
>> > how do i get a count of male vs female?
>>
>> Select e.Gender, Count(*) As Number
>> From employees e
>> Group By e.Gender
>> Order By e.Gender
>>
>> >
>> > also, is it possible, in addition to the above, get the percentage of
>> > male
>> > and female, and avg of their salary all in one statment?
>>
>> Select e.Gender, Count(*) As Number,
>> 100 * (Count(*) / t.TotalCount) As Percent,
>> Avg(e.Salary) As AverageSalary
>> From employees e
>> Cross Join (Select Count(*) As TotalCount
>> From employees e1) As t
>> Group By e.Gender
>> Order By e.Gender
>>
>> >
>> > thanks,
>> > rodchar
>>
>> Tom
>>
>>
>>


markc600

3/15/2007 5:13:00 PM

0

select sum(case when Gender='M' then 1 else 0 end) as Male,
sum(case when Gender='F' then 1 else 0 end) as Female,
count(*) as Total,
sum(case when Gender='M' then 1 else 0 end)*100/count(*) as
MalePercent,
sum(case when Gender='F' then 1 else 0 end)*100/count(*) as
FemalePercent,
sum(case when Gender='M' then Salary else 0 end)/sum(case when
Gender='M' then 1 else 0 end) as AvgMaleSalary,
sum(case when Gender='F' then Salary else 0 end)/sum(case when
Gender='F' then 1 else 0 end) as AvgFemaleSalary
from employee

rodchar

3/15/2007 6:12:00 PM

0

Thank you for the generous help you guys are great.

"rodchar" wrote:

> hey all,
> i have an employee table of the following:
> empID, Gender, Salary
>
> how do i get a count of male vs female?
>
> also, is it possible, in addition to the above, get the percentage of male
> and female, and avg of their salary all in one statment?
>
> thanks,
> rodchar