How to query the distribution (grouped by value range) of a column value in Oracle?

Question

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?

Begot

Please show the complete definition of your table (ideally as create table) – Bumboat

Answer 1

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

Kreplach

Your sql works perfectly. One further question, what if I want to know the percentage of each group at the same time? – Begot

I'm not an Oracle user, but I think they have nice functionality for that. Anyway, in ANSI SQL you can simply add "count() 100.0 / (select count(*) from distances)" to the select list. – Kreplach

Answer 2

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;

Stewartstewed

Answer 3

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;

Norge