MySQL how to get avg of values into specific range

Question

I have the following table data:

value
1
5
10.5
12
36

I want to map these values into:

range      avg
0-21       (1 + 5 + 10.5 + 12) / 4
21.001-34  0
34.001-64  36
64 +       0

Basically map each value to ranges and calculate the avg of all values in each range.

I tried to do:

select 
case
when avggeneral between 0 and 21 then ' 0-21'
when avggeneral between 21.00001 and 34 then ' 21-34'
when avggeneral between 34.00001 and 64 then ' 34-64'
else '64+'
end as 'range',
AVG(*) as 'avg'
from table

but this doesn't work... I don't know how to make the static ranges...

How can I do that in MySQL?

Methology should be: 1. Map values into these groups ( 0-21, 21-34 etc..) 2. calulate AVG in each group.

Desired output for above example is this:

range      avg
0-21       7.125
21.001-34  0
34.001-64  36
64 +       0

The range column is static. Always with 5 rows. The avg column is dynamic.. the values there are the actual AVGs of value column.

Salman A · Accepted Answer · 2018-12-03T12:51:12.810

3

You could build a list of ranges using UNION ALL and LEFT JOIN with it:

SELECT CONCAT(IFNULL(ranges.min, '∞'), '-', IFNULL(ranges.max, '∞')) AS `range`, avg(value) AS avg
FROM (
    SELECT 0 AS min, 21 AS max UNION ALL
    SELECT 21, 34 UNION ALL
    SELECT 34, 64 UNION ALL
    SELECT 64, NULL
) AS ranges
LEFT JOIN t ON (ranges.min IS NULL OR value >= ranges.min) AND
               (ranges.max IS NULL OR value <  ranges.max)
GROUP BY ranges.min, ranges.max

Note that the above query will put 20.9999 inside [0-21) and 21.0000 inside [21-34) range.

edited Dec 03 '18 at 12:51

answered Dec 03 '18 at 12:01

Salman A

262,204
82
430
521

This doesn't work. It generate one row.. I'll edit to explain – Luis Dec 03 '18 at 12:11
@Luis see revised answer. – Salman A Dec 03 '18 at 12:19
Was just typing something very similar. You should use `COALESCE(AVG(value),0)` to remove `NULL` values from the output though – Nick Dec 03 '18 at 12:22
Where does my table input comes in? – Luis Dec 03 '18 at 12:28
1

@Luis in `LEFT JOIN t` replace `t` with your table name – Nick Dec 03 '18 at 12:29

Md. Suman Kabir · Answer 2 · 2018-12-03T12:31:22.090

You can use UNION to get the desired result like below :

select '0-21' as Range1, coalesce(avg(avggeneral),0) as AVG from Table1
where avggeneral > 0 and avggeneral <= 21
union
select '21-34' as Range1, coalesce(avg(avggeneral),0) as AVG from Table1
where avggeneral > 21 and avggeneral <= 34
union
select '34-64' as Range1, coalesce(avg(avggeneral),0) as AVG from Table1
where avggeneral > 34 and avggeneral <= 64
union
select '64+' as Range1, coalesce(avg(avggeneral),0) as AVG from Table1
where avggeneral > 64

SQL HERE

LukStorms · Answer 3 · 2018-12-04T13:41:15.243

You don't really need to use those decimals.
Because for example, if the "value" equals 21, then the CASE would already return the 0-21 range before evaluating the next WHEN.

But you still need to group on the range.

And to return all the ranges, whether or not they are missing, you could left join to a sub-query with the ranges.

SELECT Ranges.`range`, COALESCE(AVG(Q.`value`), 0) as `avg`
FROM
(
  SELECT 0 as `class`, ' 0-21' as `range`
  UNION ALL SELECT 21, '21-34'
  UNION ALL SELECT 34, '34-64'
  UNION ALL SELECT 64, '64+'
) Ranges
LEFT JOIN
(
  SELECT 
   `value`,
   case
   when `value` between  0 and 21 then 0
   when `value` between 21 and 34 then 21
   when `value` between 34 and 64 then 34
   when `value` > 64 then 64
   end as rangeclass
  FROM test
) Q ON Q.rangeclass = Ranges.`class`
GROUP BY Ranges.`class`, Ranges.`range`
ORDER BY Ranges.`class`

db<>fiddle here

It doesn't work.. check the manual that corresponds to your MySQL server version for the right syntax to use near 'range, AVG(value) as avg — Luis, Dec 03 '18 at 12:20
@Luis Well, it worked when I used an actual table name. But indeed it was wrong in a way because it assumed that all ranges would be in the data. Now that's fixed with a left join to a sub-query with the ranges. — LukStorms, Dec 03 '18 at 12:53

MySQL how to get avg of values into specific range

3 Answers3

Linked