Josh Josh - 4 years ago 72
SQL Question

How to get data in ranges?

I am new to SQL and the below query is to calculate the count of vehicles which have travelled between 0 to 100 km within a time period.

select 1 as "1 - 100",count (*) from (
SELECT extract (day from start_time) as day ,place, vehicle_id,sum(distance_two_points) as distance
FROM public.datatable where start_time >= '2015-09-05 00:00:00' and start_time <= '2015-09-05 23:59:59'
and place=1 group by day, place ,veh_id
order by day,place,veh_id ) as A where distance >0 and distance<100


Here distance_two_points shows the distance in one trip and a vehicle will have more than a trip.So I take the sum of distance_two_points for each vehicle_id which gives the total distance travelled by the vehicle.

The subquery

SELECT extract (day from start_time) as day ,place, vehicle_id,sum(distance_two_points) as distance
FROM public.datatable where start_time >= '2015-09-05 00:00:00' and start_time <= '2015-09-05 23:59:59'
and place=1 group by day, place ,veh_id
order by day,place,veh_id


will return all the vehicle's total distance travelled and then I filter by

distance >0 and distance<100


Instead of one filtering I want to categorize as the following:

range count
______ ______
1-100 17
100-200 30
300-400 40
400-500 39
500-600 36


Instead of combining five queries is there a method to get the above results? Any help is appreciated.

Answer Source

You can use a case for the group by key. You seem to want something like this:

SELECT (CASE WHEN points >= 0 AND points <= 100 THEN '1-100'
             WHEN points <= 200 THEN '101-200'
             WHEN points <= 300 THEN '201-300'
             WHEN points <= 400 THEN '301-400'
             WHEN points <= 500 THEN '401-500'
             WHEN points <= 600 THEN '501-600'
        END) as range,
       COUNT(*) as length
FROM public.datatable 
WHERE start_time >= '2015-09-05' and start_time < '2015-09-06' and
      place = 1 and length>=0 and length<=100 and place=1 
GROUP BY range
ORDER BY MIN(points);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download