Group by Percentage Ranges - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Group by Percentage Ranges

Author

Topic

mchohan
Starting Member

39 Posts

Posted - 2009-07-24 : 11:03:39

I've got a table of date of births.

I got a sql function that works out the age:
select surname, dbo.fn_GetAge(p.dob,getdate())
from people

I need to display a summary breakdown by percentage as following from this data:

% under 20 :50%
% 21 to 25 :25%
% 26 to 30 :25%

Thanks in advance!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-24 : 11:10:35

something like


select 
	count(case when age <20 then age else null end)*100.0/count(*) as [<20],
	count(case when age >=20 and age<=25 then age else null end)*100.0/count(*) [21 to 25],
	count(case when age >=26 and age<=30 then age else null end)*100.0/count(*) [26 to 30]	
from 
(
select surname, dbo.fn_GetAge(p.dob,getdate()) as age
from people
) as t

Madhivanan

Failing to plan is Planning to fail

mchohan
Starting Member

39 Posts

Posted - 2009-07-24 : 11:22:29

yep, that's the one!
Thanks!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-25 : 02:04:43

quote:
Originally posted by mchohan

yep, that's the one!
Thanks!

You are welcome

Madhivanan

Failing to plan is Planning to fail

mchohan
Starting Member

39 Posts

Posted - 2009-07-29 : 09:56:08

Hi,
I'm running into a Divide By zero error on this where I've got no records.

How do you suggest to get round this?
thanks for your assistance

quote:
Originally posted by madhivanan

quote:
Originally posted by mchohan

yep, that's the one!
Thanks!
You are welcome

Madhivanan

Failing to plan is Planning to fail

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-29 : 10:04:24

[code]
IF (SELECT COUNT(*) FROM People) <> 0

select
count(case when age <20 then age else null end)*100.0/count(*) as [<20],
count(case when age >=20 and age<=25 then age else null end)*100.0/count(*) [21 to 25],
count(case when age >=26 and age<=30 then age else null end)*100.0/count(*) [26 to 30]
from
(
select surname, dbo.fn_GetAge(p.dob,getdate()) as age
from people
) as t
ELSE
SELECT 'No Data' AS [0]

[/code]

????

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

mchohan
Starting Member

39 Posts

Posted - 2009-07-29 : 10:10:29

Yes I see what you mean, cheers!

quote:
Originally posted by X002548

IF (SELECT COUNT(*) FROM People) <> 0

select
count(case when age <20 then age else null end)*100.0/count(*) as [<20],
count(case when age >=20 and age<=25 then age else null end)*100.0/count(*) [21 to 25],
count(case when age >=26 and age<=30 then age else null end)*100.0/count(*) [26 to 30]
from
(
select surname, dbo.fn_GetAge(p.dob,getdate()) as age
from people
) as t
ELSE
SELECT 'No Data' AS [0]

????

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources