CODEWITHSUNDEEP
sqlsql-server
Stevengilchrist
Stevengilchrist

Reputation: 11

Display Results in correct format in SQL

I'm trying to retrieve age data (which is in a decimal format) but I want to group the data into age brackets and display these under a column header.

Below is what I've tried and where I've got so far. However it displays the results with each age bracket as the column where as Id need the results to display like this for example:

Age range Count
20-30 462
30-40 325

Code I've used below

SELECT SUM(CASE WHEN Age <= 30 THEN 1 ELSE 0 END) AS '20-30',
        SUM(CASE WHEN Age BETWEEN 31 AND 40 THEN 1 ELSE 0 END) AS '30-40',
        SUM(CASE WHEN Age BETWEEN 41 AND 50 THEN 1 ELSE 0 END) AS '40-50',
        SUM(CASE WHEN Age BETWEEN 51 AND 60 THEN 1 ELSE 0 END) AS '50-60',
        SUM(CASE WHEN Age BETWEEN 61 AND 100 THEN 1 ELSE 0 END) AS '+60'
         FROM employeedata
WHERE NOT status IN ('Terminated', 'Retired')
And type = 'Employee'

Upvotes: 1

Views: 122

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271091

You a case expression as the aggregation key:

SELECT (CASE WHEN Age BETWEEN 31 AND 40 THEN '30-40'
             WHEN Age BETWEEN 41 AND 50 THEN'40-50'
             WHEN Age BETWEEN 51 AND 60 THEN '50-60'
             WHEN Age BETWEEN 61 AND 100 THEN '+60'
        END) as age_range, COUNT(*)
FROM employeedata
WHERE status NOT IN ('Terminated', 'Retired') AND
      type = 'Employee' 
GROUP BY (CASE WHEN Age BETWEEN 31 AND 40 THEN '30-40'
               WHEN Age BETWEEN 41 AND 50 THEN'40-50'
               WHEN Age BETWEEN 51 AND 60 THEN '50-60'
               WHEN Age BETWEEN 61 AND 100 THEN '+60'
          END);

Note: You probably want a filter WHERE age >= 31 so you don't have a NULL group for younger employees.

Upvotes: 2

Related Questions