Tyler Cox Tyler Cox - 1 month ago 12
SQL Question

More efficient query; sqlite; count group where values in range

I am working with data that essentially looks like this.

table:processed_data

sensor_id, reading, time_stamp
1,0.1,1234567890
1,0.3,1234567891
1,0.9,1234567892
1,0.32,1234567893
...


what I want to do is make a query that can make one loop through the data and count how many readings are in each category. Simple example,

categories (0-0.5,0.5-0.7,0.7-1) (I am actually planning on breaking them into 10 categories with 0.1 increments though).

This is essentially what I want, even though it isn't valid sql:

select count(reading between 0 and 0.5), count(reading between 0.5 and 0.7), count(reading between 0.7 and 1) from processed_data;


The only way I can think of doing it though, is to do an O*N operation, rather than a 1 time loop.

select count(*) as low from processed_data where reading between 0 and 0.5
union
select count(*) as med from processed_data where reading between 0.5 and 0.7
union
select count(*) as high from processed_data where reading between 0.7 and 1;


I might just resort to doing the processing in php and scan the data once, but I would prefer to have sql do it, if it can be smart enough.

Thanks for any help.

CL. CL.
Answer

You can derive the category from the value, and use that for grouping:

SELECT CAST(reading * 10 AS INTEGER),
       COUNT(*)
FROM processed_data
GROUP BY CAST(reading * 10 AS INTEGER);
Comments