Josh - 4 years ago 72

SQL Question

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.

Recommended for you: Get network issues from **WhatsUp Gold**. **Not end users.**

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**