How to Group By a numeric interval? - 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

General SQL Server Forums

New to SQL Server Programming

How to Group By a numeric interval?

Author

Topic

glpita
Starting Member

17 Posts

Posted - 2009-09-17 : 19:54:19

Hello all,

I have to deal with 2 columns of a big database. One column has dollars spent in health issues and the other has an average age ranging from 20 to 70 i.e.

Health Expenditure | Average age
---------------------------------
$ 121.2 | 56
$ 206.7 | 48
$ 86.5 | 45
... | ...

I need to write a query in SQL to aggregate the amount of money spent by each age-group organized in 5-year increments i.e. 20-25,26-30,31-35,36-40,41-45,... The result would look like:

Spent $ | Age Group
---------------------
$ 10,000 | 20-25
$ 23,000 | 26-30
and so on...

Any help is greatly appreciated!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-17 : 21:12:49

[code]select sum(spent),
case when age between 20 and 25 then '20-25'
when age between 26 and 30 then '26-30'
. . .
end
from yourtable
group by
case when age between 20 and 25 then '20-25'
when age between 26 and 30 then '26-30'
. . .
end[/code]

KH
[spoiler]Time is always against us[/spoiler]

glpita
Starting Member

17 Posts

Posted - 2009-09-17 : 23:44:41

Thanks KH.

Unfortunately when I ran the code you provided a message pops up saying:

"Syntax error (missing operator) in query expression 'CASE WHEN Age BETWEEN 50 AND 55 THEN '50-55''

By the way, I'm using MS Access SQL and apparently it doesn't allow CASE statements... Would you please suggest an alternative for MS Access SQL? What other more capable free-SQL platform (easy to use) you suggest?

Thanx

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-18 : 00:47:50

I am not familiar with Access syntax. Somebody else will help you with this.

for free SQL Server, use SQL Server 2005 / 2008 Express Edition

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-18 : 02:27:06

Try this


select sum(spent), 
       iif(age between 20 and 25 ,'20-25',iif(age between 26 and 30 ,'26-30',..)
from  yourtable
group by 
       iif(age between 20 and 25 ,'20-25',iif(age between 26 and 30 ,'26-30',..)

Madhivanan

Failing to plan is Planning to fail

glpita
Starting Member

17 Posts

Posted - 2009-09-18 : 14:26:11

It worked perfectly!

Thanks Madhivanan and KH

best regards.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-19 : 02:11:56

quote:
Originally posted by glpita

It worked perfectly!

Thanks Madhivanan and KH

best regards.

You are welcome

Post your ACCESS related questions in ACCESS forum

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources