Generating a percent aggregated table with SQL? any clue?

[Lynux]

Anyone have a tip to accomplish aggregating ((using count - distinct count some how Id assume)) the percent in a given field (example: we have a table with 3 million cases and a gender field - what would the SQL syntax be to generage a aggregated table of the percent of male vs. females?) any clues would be extremely helpful!

Thank you!
RL

 

[tlbroadbent]

I think this SQL script does what you want.

Declare @tot int
Select @tot=count(*) From CaseTbl

Select
Gender,
CaseCnt=count(*),
CasePct=100.*Count(ID)/@tot
From CaseTbl
Group By Gender

If you want the results on one line then use the following.

Declare @tot int
Select @tot=count(*) From #t

Select
MaleCnt=sum(
Case Gender
When 'M' Then CaseCnt
Else 0 End),
MalePct=sum(
Case Gender
When 'M' Then CasePct
Else 0 End),
FemaleCnt=sum(
Case Gender
When 'F' Then CaseCnt
Else 0 End),
FemalePct=sum(
Case Gender
When 'F' Then CasePct
Else 0 End)
From
(Select
Gender,
CaseCnt=count(*),
CasePct=100.*Count(ID)/@tot
From CaseTbl
Group By Gender) As a Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it if you have time.