Solved

# sql server calculate age range with case

Posted on 2012-08-14
Medium Priority
1,721 Views
Trying to calculate an age range of the records in my table.  Someone please check my syntax as something is not right.

select AGE = CASE
when BETWEEN '18' AND '25' then '18-25'
When Between '26' AND '30' then '26-30'
When Between '31' AND '35' then '31-35'
When between '36' AND '40' then '36-40'
when between '41' and '45' then '41-45'
when between '46' and '50' then '46-50'
when between '51' and '55' then '51-44'
when between '56' and '60' then '56-60'
when between '61' and '65' then '61-65'
when between '66' and '70' then '66-70'
when between '71' and '75' then '71-75'
when between '76' and '80' then '76-80'
--when <= 60 then 'Over 80'
End,
COUNT(*) as TotalVoters
From vwPersonSearch
Group by Case vf.age
when BETWEEN '18' AND '25' then '18-25'
When Between '26' AND '30' then '26-30'
When Between '31' AND '35' then '31-35'
When between '36' AND '40' then '36-40'
when between '41' and '45' then '41-45'
when between '46' and '50' then '46-50'
when between '51' and '55' then '51-44'
when between '56' and '60' then '56-60'
when between '61' and '65' then '61-65'
when between '66' and '70' then '66-70'
when between '71' and '75' then '71-75'
when between '76' and '80' then '76-80'
when <= '60' then 'Over 80'
end
0
Question by:gwarcher
• 4
• 3

LVL 25

Expert Comment

ID: 38294590
The first CASE is missing the column to evaluate ...vf.age I think.
First and second CASE statements are not the same.
Second case ... did you mean:
when > '80' then 'Over 80'
0

Author Comment

ID: 38294609
Sorry, me and greater than/less than have never gotten along.  Here is the new query, still not working...

select AGE = CASE vf.age
when BETWEEN '18' AND '25' then '18-25'
When Between '26' AND '30' then '26-30'
When Between '31' AND '35' then '31-35'
When between '36' AND '40' then '36-40'
when between '41' and '45' then '41-45'
when between '46' and '50' then '46-50'
when between '51' and '55' then '51-44'
when between '56' and '60' then '56-60'
when between '61' and '65' then '61-65'
when between '66' and '70' then '66-70'
when between '71' and '75' then '71-75'
when between '76' and '80' then '76-80'
when > '80' then 'Over 80'
End,
COUNT(*) as TotalVoters
From vwPersonSearch vf
Group by Case vf.age
when BETWEEN '18' AND '25' then '18-25'
When Between '26' AND '30' then '26-30'
When Between '31' AND '35' then '31-35'
When between '36' AND '40' then '36-40'
when between '41' and '45' then '41-45'
when between '46' and '50' then '46-50'
when between '51' and '55' then '51-44'
when between '56' and '60' then '56-60'
when between '61' and '65' then '61-65'
when between '66' and '70' then '66-70'
when between '71' and '75' then '71-75'
when between '76' and '80' then '76-80'
when > '80' then 'Over 80'
end
0

LVL 25

Expert Comment

ID: 38294622
Sorry, try this syntax for the CASE ... e.g.
CASE when vf.age BETWEEN '18' AND '25' then '18-25'
When vf.age Between '26' AND '30' then '26-30'
...
when vf.age > 80 ...
0

Author Comment

ID: 38294653
0

Accepted Solution

gwarcher earned 0 total points
ID: 38294675
Got It!

select COUNT(*) as totalVoters, * From
(
select case
when vf.age BETWEEN '18' AND '25' then '18-25'
When vf.age Between '26' AND '30' then '26-30'
When vf.age Between '31' AND '35' then '31-35'
When vf.age between '36' AND '40' then '36-40'
when vf.age between '41' and '45' then '41-45'
when vf.age between '46' and '50' then '46-50'
when vf.age between '51' and '55' then '51-44'
when vf.age between '56' and '60' then '56-60'
when vf.age between '61' and '65' then '61-65'
when vf.age between '66' and '70' then '66-70'
when vf.age between '71' and '75' then '71-75'
when vf.age between '76' and '80' then '76-80'
when vf.age > '80' then 'Over 80'
End as ageRange

) t
group by ageRange
order by ageRange
0

LVL 25

Expert Comment

ID: 38294700
Just out of curiosity ... what happened to the " AGE = " bit ... was that the problem?
0

Author Closing Comment

ID: 38309111
fixed it myself
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down ā¦
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error āConnecting to the Integration Services service on the computer failed with the following error: 'The specified service ā¦
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
###### Suggested Courses
Course of the Month17 days, 11 hours left to enroll