In SQL, how can you "group by" in ranges?

I would do this a little differently so that it scales without having to define every case:

select t.range as [score range], count(*) as [number of occurences]
from (
select FLOOR(score/10) as range
from scores) t
group by t.range

Not tested, but you get the idea...

This will allow you to not have to specify ranges, and should be SQL server agnostic. Math FTW!

SELECT CONCAT(range,'-',range+9), COUNT(range)
FROM (
SELECT
score - (score % 10) as range
FROM scores
)

Try

SELECT (str(range) + "-" + str(range + 9) ) AS [Score range], COUNT(score) AS [number of occurances]
FROM (SELECT score, int(score / 10 ) * 10 AS range FROM scoredata )
GROUP BY range;

Neither of the highest voted answers are correct on SQL Server 2000. Perhaps they were using a different version.

Here are the correct versions of both of them on SQL Server 2000.

select t.range as [score range], count(*) as [number of occurences]
from (
select case
when score between 0 and 9 then ' 0- 9'
when score between 10 and 19 then '10-19'
else '20-99' end as range
from scores) t
group by t.range


or

select t.range as [score range], count(*) as [number of occurrences]
from (
select user_id,
case when score >= 0 and score< 10 then '0-9'
when score >= 10 and score< 20 then '10-19'
else '20-99' end as range
from scores) t
group by t.range

select t.range as score, count(*) as Count
from (
select UserId,
case when isnull(score ,0) >= 0 and isnull(score ,0)< 5 then '0-5'
when isnull(score ,0) >= 5 and isnull(score ,0)< 10 then '5-10'
when isnull(score ,0) >= 10 and isnull(score ,0)< 15 then '10-15'
when isnull(score ,0) >= 15 and isnull(score ,0)< 20 then '15-20'
else ' 20+' end as range
,case when isnull(score ,0) >= 0 and isnull(score ,0)< 5 then 1
when isnull(score ,0) >= 5 and isnull(score ,0)< 10 then 2
when isnull(score ,0) >= 10 and isnull(score ,0)< 15 then 3
when isnull(score ,0) >= 15 and isnull(score ,0)< 20 then 4
else 5 end as pd
from score table
) t

group by t.range,pd order by pd

I'm here because i have similar question but i find the short answers wrong and the one with the continuous "case when" is to much work and seeing anything repetitive in my code hurts my eyes. So here is the solution
```
SELECT --MIN(score), MAX(score),
[score range] = CAST(ROUND(score-5,-1)AS VARCHAR) + ' - ' + CAST((ROUND(score-5,-1)+10)AS VARCHAR),
[number of occurrences] = COUNT(*)
FROM order
GROUP BY CAST(ROUND(score-5,-1)AS VARCHAR) + ' - ' + CAST((ROUND(score-5,-1)+10)AS VARCHAR)
ORDER BY MIN(score)


```

For PrestoSQL/Trino applying answer from Ken

[To see links please register here]

```
select t.range, count(*) as "Number of Occurance", ROUND(AVG(fare_amount),2) as "Avg",
ROUND(MAX(fare_amount),2) as "Max" ,ROUND(MIN(fare_amount),2) as "Min"
from (
select
case
when trip_distance between 0 and 9 then ' 0-9 '
when trip_distance between 10 and 19 then '10-19'
when trip_distance between 20 and 29 then '20-29'
when trip_distance between 30 and 39 then '30-39'
else '> 39'
end as range ,fare_amount
from nyc_in_parquet.tlc_yellow_trip_2022) t
where fare_amount > 1 and fare_amount < 401092
group by t.range;

range | Number of Occurance | Avg | Max | Min
-------+---------------------+--------+-------+------
0-9 | 2260865 | 10.28 | 720.0 | 1.11
30-39 | 1107 | 104.28 | 280.0 | 5.0
10-19 | 126136 | 43.8 | 413.5 | 2.0
> 39 | 42556 | 39.11 | 668.0 | 1.99
20-29 | 19133 | 58.62 | 250.0 | 2.5
```

In postgres (where `||` is the string concatenation operator):

select (score/10)*10 || '-' || (score/10)*10+9 as scorerange, count(*)
from scores
group by score/10
order by 1

gives:

scorerange | count
------------+-------
0-9 | 11
10-19 | 14
20-29 | 3
30-39 | 2

## And here's how to do it in T-SQL:

````sql
DECLARE @traunch INT = 1000;

SELECT
CONCAT
(
FORMAT((score / @traunch) * @traunch, '###,000,000')
, ' - ' ,
FORMAT((score / @traunch) * @traunch + @traunch - 1, '###,000,000')
) as [Range]
, FORMAT(MIN(score), 'N0') as [Min]
, FORMAT(AVG(score), 'N0') as [Avg]
, FORMAT(MAX(score), 'N0') as [Max]
, FORMAT(COUNT(score), 'N0') as [Count]
, FORMAT(SUM(score), 'N0') as [Sum]
FROM scores
GROUP BY score / @traunch
ORDER BY score / @traunch
````

[![enter image description here][1]][1]


[1]:

```
SELECT
COUNT(*) AS number_of_occurances,
FLOOR(scores / 10) * 10 AS scores_in_range
FROM ScoreTable
GROUP BY scores_in_range
ORDER BY scores_in_range DESC;
```

SQL Standard defines `WIDTH_BUCKET( <expr> , <min_value> , <max_value> , <num_buckets>)` function:

SELECT WIDTH_BUCKET(score, 0, 50, 5) AS bucket_num, COUNT(*)
FROM tab
GROUP BY WIDTH_BUCKET(score, 0, 50, 5)
ORDER BY bucket_num;

For input:

CREATE TABLE tab(score INT);

INSERT INTO tab(score) VALUES (1),(2),(9),(10),(11),(22),(23),(41);

Output:

bucket_num count
1 3
2 2
3 2
5 1

---

Human-readable bucket range:

SELECT CONCAT((WIDTH_BUCKET(score, 0, 50, 5)-1)*10, '-', WIDTH_BUCKET(score, 0, 50, 5)*10-1) AS bucket_num,
COUNT(*)
FROM tab
GROUP BY CONCAT((WIDTH_BUCKET(score, 0, 50, 5)-1)*10, '-', WIDTH_BUCKET(score, 0, 50, 5)*10-1)
ORDER BY bucket_num;

Output:

bucket_num count
0-9 3
10-19 2
20-29 2
40-49 1

**[db<>fiddle demo](

[To see links please register here]

)**

---

[T612, Advanced OLAP operations](

[To see links please register here]

)
>
> Transact-SQL partially supports this feature. *Transact-SQL does not support the WIDTH_BUCKET*, PERCENT_RANK, and CUME_DIST functions or the WINDOW and FILTER clauses.