SQL Server – Categorize Age Ranges and Display in One Column

sql serversql-server-2008

I want to see the data as follows

Member   Age  Category
Test1    25    Under 30
Test2    55    51-60
Test3    67    Above 60

I have used the below query (from an answer to my previous question) to calculate the age from DOB and current date and then categorize each age under one name.

This query is creating separate columns, instead I want to see all the categories under one column so that I can group this category column

WITH AgeData as
(
SELECT
    Username,
    Birthdate,
    DATEDIFF(YEAR, birthdate, GETDATE()) AS AGE
FROM @Table)
SELECT
    Username,
    Birthdate,
    Age,
    CASE
        WHEN AGE < 30 THEN 1
        ELSE 0
    END AS 'Under 30',
    CASE
        WHEN AGE BETWEEN 31 AND 40 THEN 1
        ELSE 0
    END AS '31 - 40',
    CASE
        WHEN AGE BETWEEN 41 AND 50 THEN 1
        ELSE 0
    END AS '41 - 50',
    CASE
        WHEN AGE > 50 THEN 1
        ELSE 0
    END AS 'Over 50'
FROM AgeData

Best Answer

Instead of using multiple CASE expressions, one for each group, just combine them into a single expression. Then, in the THEN clause you can use something more descriptive to group on later. In the example below I used the column aliases you provided.

WITH AgeData as
(
  SELECT [Username],
         [Birthdate],
         DATEDIFF(YEAR, [Birthdate], GETDATE()) AS [AGE]
  FROM @table
),
GroupAge AS
(
  SELECT [Username],
         [Birthdate],
         [Age],
         CASE
             WHEN AGE < 30 THEN 'Under 30'
             WHEN AGE BETWEEN 31 AND 40 THEN '31 - 40'
             WHEN AGE BETWEEN 41 AND 50 THEN '41 - 50'
             WHEN AGE > 50 THEN 'Over 50'
             ELSE 'Invalid Birthdate'
         END AS [Age Groups]
  FROM AgeData
)
SELECT COUNT(*) AS [AgeGrpCount],
       [Age Groups]
FROM GroupAge
GROUP BY [Age Groups];
Related Question