Group by Age Range

Question

Post reply

Group by Age Range

Viewing 5 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

I would do the whole thing this way:

declare @Total float;

select @Total =

(select count(*)

from #Person);

select

'0-10' as Age,

count(*) as Total,

count(*)/@Total as Percentage

from #Person

where age between 0 and 10

union all

select

'Not Entered',

count(*),

count(*)/@Total

from #Person

where age is null

and personid not in

(select personid

from #Person

where age is not null);

It'll be much more efficient than what you've got so far, and it'll give you an accurate count on the final query. Just copy and paste the firsts bit and plug in each of your ranges.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Mark Cowne One Orange Chip Points: 26986 More actions · Answer 2

Here's another way

WITH AgeRanges(AgeDesc,AgeMin,AgeMax) AS(

SELECT '0-10',0,10

UNION ALL

SELECT '11-20',11,20

UNION ALL

SELECT '21-30',21,30

UNION ALL

SELECT '31-40',31,40

UNION ALL

SELECT '41-50',41,50

UNION ALL

SELECT '51-60',51,60

UNION ALL

SELECT '61-70',61,70

UNION ALL

SELECT '71-80',71,80

UNION ALL

SELECT '81-90',81,90

UNION ALL

SELECT '91-100',91,100

UNION ALL

SELECT '100+',101,9999

UNION ALL

SELECT 'Not Entered',NULL,NULL),

PersonAges AS (

SELECT personId,MAX(Age) AS Age FROM dbo.Person GROUP BY personId

)

SELECT AgeDesc,

COUNT(personId) AS Total,

COUNT(personId)*1.0 / (SELECT COUNT(Age) FROM PersonAges) AS Percentage

FROM AgeRanges

LEFT OUTER JOIN PersonAges ON (Age BETWEEN AgeMin AND AgeMax) OR (Age IS NULL AND AgeMin IS NULL)

GROUP BY AgeDesc

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

David Jackson SSCertifiable Points: 6414 More actions · Answer 3

Thanks to both of you. Marks solution is interesting as it uses a 2005/08 feature I've seen but never used. This is my first position where SQL > 2000! :w00t:

My original stab at it returns this

AgeTotalPercentage

0-109080.049942247401

11-2014000.077003465155

21-3016640.091524118585

31-4015870.087288928001

41-5017260.094934272042

51-6016040.088223970078

61-7021420.117815301688

71-8031440.172927781750

81-9032120.176667950057

91-1007710.042406908310

100+230.001265056927

Not Entered220010.547533721566

I get this from GSquared query, altered to point to the real table

AgeTotalPercentage

0-109080.022588750404259

Not Entered24350.0605766599497475

So the Not entered looks good to me, but the 0-10 range percentage differs. Marks query returns

AgeDescTotalPercentage

0-10 9080.049934007919

11-2014000.076990761108

21-3016640.091509018917

31-4015870.087274527056

41-5017270.094973603167

51-6016040.088209414870

61-7021430.117850857897

71-8031450.172954245490

81-9032120.176638803343

91-1007710.042399912010

100+230.001264848218

Not Entered20740.114056313242

Which agrees with my 0-10 count, but differs from GSquared's Not Entered total. As I'm in the UK I suspect I have some time to figure it out, and I'll be mighty proud if I get my head round Marks code. I'll post back any findings.

Once again, many thanks to you both

Dave J

http://glossopian.co.uk/
"I don't know what I don't know."

David Jackson SSCertifiable Points: 6414 More actions · Answer 4

Cracked it, thanks to GSquared and Marks help. I noticed that Mark results differed from mine by one on 3 age groups, 41-50, 61-70 and 71-80, and frankly as I don't know this 'with' stuff works I concentrated on GSquared's solution. To cut a long story short I added this to the top of the script:

declare @notEntered float, @notEnteredPercentage float

select @total = COUNT(distinct personid) from person

select @notEntered = count(*)

from person

where age is null

and personid not in

(select personid

from person

where age is not null);

select @notEnteredPercentage = @notEntered/@total

and replaced the last select in the union with this

select

'Not Entered', @notEntered, round(@notEnteredPercentage , 3)

works a charm.

Apologies for saying GSquareds percentage was off earlier, my bad. I changed count(*) to count(age) and forgot I had done so. :blush:

Edit: I think I needed to on reflection.

Thanks guys.

Dave J

http://glossopian.co.uk/
"I don't know what I don't know."

GSquared SSC Guru Points: 260824 More actions · Answer 5

Glad we could help.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon