Group by Age range - 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 Age range

Author

Topic

HGClubTec
Starting Member

12 Posts

Posted - 2008-11-18 : 21:27:24

This is my first post, so I apologize if I miss needed information. Here's what I need:

column 1 - Ages 30-34 - # of ppl in age range - percent of ppl in age range
colum 2 - ages 35 - 39 - # of ppl in age range - percent of ppl in age range
etc
etc

what I have in my database is: ID, Age

This cannot be as difficult as I'm making it and I've come to a stopping point, so I'm asking for help.

TIA!!!

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-18 : 21:42:49

Can you give sample data and expected output?

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-11-19 : 00:05:34

[code] start with this
SELECT MAX(CASE WHEN Age BETWEEN 30 AND 34 THEN COUNT(ID) END) AS '30-34'
, MAX(CASE WHEN Age BETWEEN 35 AND 39 THEN COUNT(ID) END) AS '35-39'
.
.
FROM tbl[/code]

[code]"There is only one difference between a dream and an aim. A dream requires soundless sleep to see, whereas an aim requires sleepless efforts to achieve..!!"
[/code]

HGClubTec
Starting Member

12 Posts

Posted - 2008-11-19 : 08:31:31

When I try that code - I get
"Cannot perform an aggregate function on an expression containing an aggregate or a subquery."

For more information on my data and output:

Data in BirthdayTable:
PrimaryID, Name, Birthdate, Age

Hopeful Output:
Ages30-34, Count of ppl in Range, % of Total
Ages35-39, Count of ppl in Range, % of Total

where each section is it's own column.

Does that help?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-19 : 08:37:07

[code]
SELECT COUNT(CASE WHEN Age BETWEEN 30 AND 34 THEN ID ELSE NULL END) AS [30-34 Count]
, COUNT(CASE WHEN Age BETWEEN 35 AND 39 THEN ID ELSE NULL END) AS [35-39 Count],
COUNT(CASE WHEN Age BETWEEN 30 AND 34 THEN ID ELSE NULL END)*1.0/COUNT(ID) AS [30-34 Percent],
COUNT(CASE WHEN Age BETWEEN 35 AND 39 THEN ID ELSE NULL END)*1.0/COUNT(ID) AS [35-39 Percent]
.
.
FROM tbl
[/code]

HGClubTec
Starting Member

12 Posts

Posted - 2008-11-19 : 08:50:41

BEAUTIFUL!!! THANK YOU - THANK YOU!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-19 : 08:54:12

Cheers

shijobaby
Starting Member

44 Posts

Posted - 2009-08-21 : 06:24:43

The ways to avoid this error is simple

just look into my post

http://sqlerrormessages.blogspot.com/2009/08/sql-server-error-message-msg-130-cannot.html

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources