SQL Server: How to make count(*) of Ages groups-CodePudding

I have a Table Users with users and a Age column with their ages.

Name	Age
User1	19
User2	25
User3	75

I want to show their ages and count it. But I don't want to make the count for each age specifically. I want to do it in groups of ages like this:

Age group	Count
<18	5
19-34	2
>75	2

How can I do this?

CodePudding user response：

Categorize the records using case statements.
Group them and count

    Select a.AgeGroup, count(*) Count from (
select 
Case when  Age < 19 then '<19' 
     when Age between 19 and 34 then '19-34'
     when  Age > 75 then '>75' end AgeGroup 
from age ) a
group by a.AgeGroup

CodePudding user response：

First of all, please note your desired output for that question can't be obtained from the source data you've provided us with.

Second gap is between "19-34" and "> 75" that's why I've added "else" in the output

Another thing to mention is to gap in your logic between "<18" and "19-34" - in this way you'll loose those who are 18 years old. It has to be either "<= 18" or ( "< 18" and "18-34").

The condition "> 75" will ignore those who are 75 years old too.

But here is the answer based on your question and the source data

select case when age < 18 then '< 18' when age between 19 and 34 then '19-34' when age > 75 then '> 75' else 'else' end age_group,
       count(1)
  from test_data
 group by case when age < 18 then '< 18' when age between 19 and 34 then '19-34' when age > 75 then '> 75' else 'else' end