Suppose there is a table called DISTANCES in Oracle with a float type column named distance. The range of distance is from [0, 1000]. I want to know the distribution of the distances, for example, how many rows are in each of the following ranges: (0, 10], (10, 50], (50, 100], (100, 500], … (5000, 10000].
How can I build this SQL query?
Advertisement
Answer
Use a derived table to put each distance into its group. Then GROUP BY
and count
:
x
select dist_group, count(*)
from
(
select case when distance between 0 and 10 then '(0, 10)'
when distance between 10 and 50 then '(10, 50)'
when distance between 5000 and 10000 then '(5000, 10000)' end as dist_group
from distances
)
group by dist_group