Age bands in sql. Is it possible? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Age bands in sql. Is it possible?

Author

Topic

Kift
Starting Member

14 Posts

Posted - 2008-02-18 : 07:27:16

I've created a dataset that have a number of people in it that I wish to group by age and sex.

The closest code I have found for this is:

Select IIf(ClientAge < 16, "Grp1 <16", IIf(ClientAge < 21, "Grp2 16-20", IIf(ClientAge < 30, "Grp3 21-30", "Grp4 >30"))) As AgeBand
From YourTable

However, this was from an access forum and whilst I tried to adapt it for what I needed, I failed as the code did not work and also it only contains grouping by one variable, age, and doesn't allow for sex.

I was wondering if what I'm trying to do is possible, and if so, is it possible to save the output to a table in the database, or if thats not possible and external text ot .xls file.

Thanks for your time and help.

K.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-18 : 07:30:59

[code]Select
Case
When ClientAge between 0 and 15 then "Grp1 <16"
When ClientAge between 16 and 20 then "Grp2 16-20"
...
End as AgeBand
From YourTable[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-02-18 : 08:19:20

I making some assumptions about column names

Select
Case
When ClientAge between 0 and 15 AND gender = 'Male' then 'Grp1 <16 Male'
When ClientAge between 0 and 15 AND gender = 'Female' then 'Grp1 <16 Female'
When ClientAge between 16 and 20 and gender = 'Male' then 'Grp2 16-20 Male'
When ClientAge between 16 and 20 and gender = 'Female' then 'Grp2 16-20 Female'
End as AgeBand
From YourTable

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

Kift
Starting Member

14 Posts

Posted - 2008-02-18 : 10:23:08

Thanks very much for your help and time on this.

Works perfectly.

Thanks again.

K.

Kift
Starting Member

14 Posts

Posted - 2008-02-19 : 07:54:30

Sorry, I have a follow-on query regarding this.
The case statement worked fine and I produced the agebands of 0-4, 5-9 ... 85+ for what I needed and used the following query to return the information by ageband and sex:

select sex, ageband, risk into riskoutput from risk
group by sex, ageband

And it produces the output. However I also need the query to return on the same output a breakdown of total risk by sex and a total.
eg output now:

Sex ageband Risk
Female 00 - 04 27597
Male 00 - 04 29236
Female 05 - 09 35467
Male 05 - 09 37181
Female 10 - 14 39059
Male 10 - 14 40986
Female 15 - 19 38624
Male 15 - 19 38989
Female 20 - 24 41458
Male 20 - 24 39240
Female 25 - 29 40522
Male 25 - 29 39813
Female 30 - 34 51486
Male 30 - 34 50116
Female 35 - 39 54381
Male 35 - 39 53593
Female 40 - 44 48268
Male 40 - 44 48572
Female 45 - 49 40976
Male 45 - 49 41836
Female 50 - 54 40470
Male 50 - 54 40726
Female 55 - 59 40067
Male 55 - 59 40547
Female 60 - 64 29819
Male 60 - 64 29476
Female 65 - 69 27196
Male 65 - 69 25512
Female 70 - 74 25802
Male 70 - 74 22197
Female 75 - 79 23174
Male 75 - 79 17330
Female 80 - 84 19722
Male 80 - 84 12170
Female 85+ 22883
Male 85+ 10354

Output I would like to get (produced in excel):
Sex ageband Risk
Female 00 - 04 27597
Male 00 - 04 29236
Female 05 - 09 35467
Male 05 - 09 37181
Female 10 - 14 39059
Male 10 - 14 40986
Female 15 - 19 38624
Male 15 - 19 38989
Female 20 - 24 41458
Male 20 - 24 39240
Female 25 - 29 40522
Male 25 - 29 39813
Female 30 - 34 51486
Male 30 - 34 50116
Female 35 - 39 54381
Male 35 - 39 53593
Female 40 - 44 48268
Male 40 - 44 48572
Female 45 - 49 40976
Male 45 - 49 41836
Female 50 - 54 40470
Male 50 - 54 40726
Female 55 - 59 40067
Male 55 - 59 40547
Female 60 - 64 29819
Male 60 - 64 29476
Female 65 - 69 27196
Male 65 - 69 25512
Female 70 - 74 25802
Male 70 - 74 22197
Female 75 - 79 23174
Male 75 - 79 17330
Female 80 - 84 19722
Male 80 - 84 12170
Female 85+ 22883
Male 85+ 10354
Female 646971
Male 617874
Total 1264845

As can be seen, there are three extra rows at the bottom. Is it possible to calculate these in sql?

Thanks a lot.

K.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources