Get frequency distribution of decimal range in MySQL

Question

I am looking for an elegant way (from a syntax point of view, not necessarily effective) to get the frequency distribution of the decimal range.

For example, I have a table with a rating that can be negative or positive. I want to get the frequency of rows with a rating of a certain range. -... - from -140.00 to -130.00): 5 - from -130.00 to -120.00): 2 - [-120.00 to -110.00): 1 -... - [120.00 to 130.00): 17 - etc.

[i to j) means i inclusive for j exceptional.

Thanks in advance.

Answer 1

You can pretty closely use 'select floor (rating / 10), count (*) from (table) group by 1'

Answer 2

I was thinking of seomthing, which could do many levels such as

DELIMITER $$ CREATE PROCEDURE populate_stats() BEGIN DECLARE range_loop INT Default 500 ; simple_loop: LOOP SET the_next = range_loop - 10; Select sum(case when range between range_loop and the_next then 1 else 0 end) from table, IF the_next=-500 THEN LEAVE simple_loop; END IF; END LOOP simple_loop; END $$ usage: call populate_stats(); 

Will handle 100 ranges from 500-490, 490-480, ... -480 - -490, -490 - -500

Answer 3

assuming a finite number of ranges.

 Select sum(case when val between -140 to -130 then 1 else 0 end) as sum-140_to_-130, sum(Case when val between -130 to -120 then 1 else 0 end) as sum-130_to_-140, ... FROM table 

and if not, you can use dynamic SQL to generate a selection that allows multiple ranges, however you may run into a column constraint.

Answer 4

Just enter the ranges you want into the table and use this to distinguish between the values.

 -- SET search_path='tmp'; DROP TABLE measurements; CREATE TABLE measurements ( zval INTEGER NOT NULL PRIMARY KEY ); INSERT INTO measurements (zval) SELECT generate_series(1,1000); DELETE FROM measurements WHERE random() < 0.20 ; DROP TABLE ranges; CREATE TABLE ranges ( zmin INTEGER NOT NULL PRIMARY KEY , zmax INTEGER NOT NULL ); INSERT INTO ranges(zmin,zmax) VALUES (0, 100), (100, 200), (200, 300), (300, 400), (400, 500), (500, 600), (600, 700), (700, 800), (800, 900), (900, 1000) ; SELECT ra.zmin,ra.zmax , COUNT(*) AS zcount FROM ranges ra JOIN measurements me ON me.zval >= ra.zmin AND me.zval < ra.zmax GROUP BY ra.zmin,ra.zmax ORDER BY ra.zmin ; 

Results:

  zmin | zmax | zcount ------+------+-------- 0 | 100 | 89 100 | 200 | 76 200 | 300 | 76 300 | 400 | 74 400 | 500 | 86 500 | 600 | 78 600 | 700 | 75 700 | 800 | 75 800 | 900 | 80 900 | 1000 | 82 (10 rows)