SQL Server Case expression to count multiple groupings

y3bcpkx1 于 2023-08-02 发布在其他

关注(0)|答案(2)|浏览(101)

I need to group data by its aging. In my sample table, I have 1 column (age) where the numbers range from 1 to 100.. I want to get counts on the number that are <=10, >10, >30 >60 and >90

This is the case expression I used.

select age_bracket, count(*) as nbr  
from 
(
select 
case when age > 90 then 'Nbr >90 days'
when age > 60 then 'Nbr >60 days'
when age > 30 then 'Nbr >30 days'
when age > 10 then 'Nbr >10 days'
when age <=10 then 'Nbr <=10 days'
else 'n/a' end as age_bracket    
from [AGE]
)z
group by age_bracket

These are the results:

Age Bracket     Nbr
Nbr <=10 days   10
Nbr >10 days    20
Nbr >30 days    30
Nbr >60 days    30
Nbr >90 days    10

However, this is what I want to see:

Age Bracket     Nbr
Nbr <=10 days   10
Nbr >10 days    90
Nbr >30 days    70
Nbr >60 days    40
Nbr >90 days    10

Can this be done using a case expression? If so, how would I structure the expression to return the age bracket column and the values in the Nbr column above?

Any help you can provide would be appreciated.

Here is a sample of the data. 100 rows (value starts at 1 ends at 100)

If you put this in Excel and count how many are

>90 you get 10
>60 you get 40 (60 to 100 = 40)
>30 you get 70 (30 to 100 = 70)
>10 you get 90 (10 to 100 = 90)

sql-server

来源：https://stackoverflow.com/questions/76733019/case-expression-to-count-multiple-groupings

2条答案

按热度按时间

p5fdfcr11#

You can use where and union results. ie:

select 'Nbr <= 10 days' as age_Bracket, count(*) from [Age] where age <= 10
  union
select 'Nbr > 10 days' as age_Bracket, count(*) from [Age] where age > 10
  union
select 'Nbr > 30 days' as age_Bracket, count(*) from [Age] where age > 30
  union
select 'Nbr > 60 days' as age_Bracket, count(*) from [Age] where age > 60
  union
select 'Nbr > 90 days' as age_Bracket, count(*) from [Age] where age > 90;

赞(0）回复(0）举报 2023-08-02

jpfvwuh42#

I understood your question as you're after conditional aggregation , this means your CASE will be used within COUNT :

SELECT 
  COUNT(CASE WHEN age <= 10 then 1 END) AS "Nbr <=10 days",
  COUNT(CASE WHEN age > 10 then 1 END) AS "Nbr >10 days",
  COUNT(CASE WHEN age > 30 then 1 END) AS "Nbr >30 days",
  COUNT(CASE WHEN age > 60 then 1 END) AS "Nbr >60 days",
  COUNT(CASE WHEN age > 90 then 1 END) AS "Nbr >90 days"
FROM [AGE];

Here an example with some sample data: db<>fiddle

If I was wrong and you need the data as separate rows, you could either let your application reform the result of the above query or use UNION ALL rather than CASE as explained in Cetin's answer .

The linked fiddle shows both queries and their result.

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server Case expression to count multiple groupings

2条答案

相关问题

热门标签

最新问答