?
Solved

sql server calculate age range with case

Posted on 2012-08-14
7
Medium Priority
?
1,721 Views
Last Modified: 2012-08-19
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
Comment
Question by:gwarcher
  • 4
  • 3
7 Comments
 
LVL 25

Expert Comment

by:lwadwell
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

by:gwarcher
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

by:lwadwell
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:gwarcher
ID: 38294653
I tried that as well...the query executed but produced no results
0
 

Accepted Solution

by:
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

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

Author Closing Comment

by:gwarcher
ID: 38309111
fixed it myself
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question