Reputation: 3605
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?
Upvotes: 6
Views: 25657
Reputation: 11
Just another option with different syntax that I have used to filter on a specific column that should also work:
select count(distance) filter(where 0<= distance AND distance<=10) as '(0, 10)'
select count(distance) filter(where 10< distance AND distance <=20) as '(10, 20)'
...
from distance;
Upvotes: 1
Reputation: 44786
Use a derived table to put each distance into its group. Then GROUP BY
and count
:
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
) dt
group by dist_group
Upvotes: 14
Reputation: 168232
SELECT COUNT( CASE WHEN 0 <= distance AND distance <= 10 THEN distance END ) AS in_range_0_10,
COUNT( CASE WHEN 10 < distance AND distance <= 50 THEN distance END ) AS in_range_10_50,
COUNT( CASE WHEN 50 < distance AND distance <= 100 THEN distance END ) AS in_range_50_100,
COUNT( CASE WHEN 100 < distance AND distance <= 500 THEN distance END ) AS in_range_100_500,
COUNT( CASE WHEN 500 < distance AND distance <= 1000 THEN distance END ) AS in_range_500_1000
FROM Distance;
Upvotes: 3