categorize by Age and then to count by Agecat & Gender (single query)

[katrina11]

Hi Experts,

1. I need first to categorize patients by Age:

If memberAge in (21,22,23,24 ) then AgeCat=’Age 21-24’
If memberAge in (25,26,27,28,29) then AgeCat=’Age 25-29’ and so on

2. And group them by Age Category and Gender in order to get a count.

How can I implement these steps in a single query?

Could you please help me with it? Thank you!

Katrin

 

[r937]

SELECT CASE WHEN memberAge IN (21,22,23,24 ) 
            THEN 'Age 21-24'
            WHEN memberAge IN (25,26,27,28,29) 
            THEN 'Age 25-29'  
            ELSE 'and so on'
        END AS AgeCat
     , Gender
     , COUNT(*) AS count
  FROM daTable
GROUP
    BY AgeCat
     , Gender

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[JonFer]

If you can create tables in your DB, then you could make a table with the from and to ages and maybe the age range description. Then join to your table with:

Floor(Age) Between AgeFrom and AgeTo

This helps keep your SQL simple, makes it easy to later change the age ranges, and allows you to use the same age ranges for multiple queries.

 

[katrina11]

r937,

Thank you for the response. I tried to run the query. However, after I had executed, system generated the following error message pointing to the ageCat line at the bottom(Group clause)

Msg 207, Level 16, State 1, Line 21
Invalid column name 'AgeCat'.


What could be wrong ?

 

[gmmastros]

Katrina,

The error is caused by a columns alias (AgeCat) being used in a group by clause. There are several ways to correct this problem. The method below shows a derived table method.

SELECT AgeCategories.AgeCat
     , Gender
     , COUNT(*) AS count
  FROM daTable
       Inner Join (
          Select MemberId,
                 CASE WHEN memberAge IN (21,22,23,24 ) 
                      THEN 'Age 21-24'
                      WHEN memberAge IN (25,26,27,28,29) 
                      THEN 'Age 25-29'  
                      ELSE 'and so on'
                 END AS AgeCat
          From daTable
          ) As AgeCategories
          On daTable.MemberId = AgeCategories.MemberId
GROUP
    BY AgeCat
     , Gender

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[r937]

try it like this...

SELECT AgeCat
     , Gender
     , COUNT(*) AS howmany
  FROM ( SELECT CASE WHEN memberAge IN (21,22,23,24 )
                     THEN 'Age 21-24'  
                     WHEN memberAge IN (25,26,27,28,29)  
                     THEN 'Age 25-29'
                     ELSE 'and so on' 
                 END AS AgeCat
              , Gender
           FROM daTable ) AS t
GROUP
    BY AgeCat
     , Gender

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[r937]

george, why da join?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[gmmastros]

rudy,

No reason in particular... I guess I didn't look close enough at the query.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom