1

I want to create an histogram based on price column values from equipment table.

I know I can count the frequency of prices in an interval I can set, but the problem is that I won't get the ranges with 0 frequency.

so if I want an interval of 200 and the values are:

0,55,150,420,490,550

I will only get the ranges:

0-200, count = 3

400-600, count = 3

as a solution I can write explicitly the ranges, like:

SELECT 
count(CASE WHEN price>= 0 AND price < 200 THEN 1 END) AS '[0 - 200]',
count(CASE WHEN price>= 200 AND price < 400 THEN 1 END) AS '[200 - 400]'
...
...
from equipment
group by eq_type;

if I want to set multiple ranges, let's say from 0 to 10000 in steps of 200, is there a way I can create an array/series and use it in the query instead of typing 50 rows of the above code?

1 Answer 1

1

You need to create synthetic table containing all the intervals in Your timeline range. Or the numbers table when range limits are easily calculated from the range number.

Example:

CREATE TABLE test (val INT);
INSERT INTO test VALUES (0),(55),(150),(420),(490),(550);

SELECT (nums.num-1)*200 mini, nums.num*200 maxi, COUNT(test.val) cnt
FROM (SELECT 1 num UNION ALL SELECT 2 UNION ALL SELECT 3) nums
LEFT JOIN test ON (nums.num-1)*200 <= test.val AND test.val < nums.num*200
GROUP BY nums.num;

The numbers count is to be equal (or more, not less) to the ranges count.

If You want to change the amount of ranges dynamically influenced by data values range (with some reasonable limit) You may use the next method to create synthetic table nums (for example if ranges count guaranteed less than 1000):

SELECT nums1.num*100+nums2.num*10+nums3.num num
FROM (SELECT 0 num UNION SELECT 1 UNION ... UNION SELECT 9) nums1,
     (SELECT 0 num UNION SELECT 1 UNION ... UNION SELECT 9) nums2,
     (SELECT 0 num UNION SELECT 1 UNION ... UNION SELECT 9) nums3

To cut off the upper empty ranges use additional condition like

WHERE ... AND nums.num * @step <= (SELECT MAX(val) FROM datatable)
1
  • thanks for the answer, I can generate the table with the desired ranges but I can't implement the join condition in your second example. how can I change this to match the second example you gave? (nums.num-1)*200 <= test.val AND test.val < nums.num*200
    – Arie
    Jun 6, 2018 at 11:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.