How to group Ages with case-CodePudding

Someone knows how i can group ages witch the CASE statement?

I got the ages 14,15,16,17,18,19,20,21,22,23,24,25. I want groups like <18,19,20,>21

I started like:

Case age when BETWEEN 14 AND 18 THEN sum(age)
         when >=21 THEN sum(age)
         ELSE age END as age_groups

But i think aggregate functions don't work in CASE statements, at least it brings up an error.

CodePudding user response：

CASE statement would work, you have a syntax error, your query would be:

SELECT Case  when age BETWEEN 14 AND 18 THEN '14 - 18'
         when age >=21 THEN '>=21'
         ELSE age END as age_groups, SUM(age)
FROM YOUR_TABLE
GROUP BY Case  when age BETWEEN 14 AND 18 THEN '14 - 18'
         when age >=21 THEN '>=21'
         ELSE age END

CodePudding user response：

Another neat alternative is to place the CASE inside a CROSS APPLY (VALUES so you can then refer to it in other parts of the query without repetition:

SELECT
  v.age_group,
  SUM(Age)
FROM age_groups ag
CROSS APPLY (VALUES (
    CASE WHEN ag.age BETWEEN 14 AND 18 THEN '14 - 18'
         WHEN ag.age >= 21 THEN '>=21'
         ELSE CAST(ag.age AS varchar(10)) END
) ) v(age_group)
GROUP BY v.age_group

CodePudding user response：

Use a CTE to calculate the age_groups, and then you can normally sum the ages for each group.

WITH age_groups AS (
  SELECT Age,
         Case  when age BETWEEN 14 AND 18 THEN '14 - 18'
               when age >=21 THEN '>=21'
               ELSE age END as age_group
  FROM YOUR_TABLE
)
SELECT age_group, sum(Age)
FROM age_groups
GROUP BY age_group

CodePudding user response：

You can use Case inside Sum():

select 
    Sum(Case when age < 18 then 1 end) '<18',
    Sum(Case when age = 19 then 1 end) '19',
    Sum(Case when age = 20 then 1 end) '20',
    Sum(Case when age > 20 then 1 end) '>20'
from myTable;

DBFiddle demo is here

EDIT: If you meant to get the results vertically, still keep it simple:

select '<18' as ageGroup, Sum(Case when age < 18 then 1 end) total from myTable
union
select '19', Sum(Case when age = 19 then 1 end) from myTable
union
select '20', Sum(Case when age = 20 then 1 end) from myTable
union
select '20>', Sum(Case when age > 20 then 1 end) from myTable;