mysql LEFT JOIN followed by GROUP BY with BETWEEN Ranges

Question

I am stumped with how I should proceed. Here is my current LEFT JOIN command which works just fine:

SELECT t1.avg_temperature as T_aver,
       t2.new_confirmed as count 
FROM 3_day_avg as t1 
LEFT JOIN table2 as t2 on t1.date = t2.date

And this works great to make this table:

T_aver    |count|
-----------------
-0.2      | 2   |
3         | 2   |
5         | 1   |
-2.3      | 4   |
22        | 0   |

But now I want to take it one step further and group by ranges of T_aver (bins like 0-5, 6-10, 11-15, etc) and SUM() the count column. For example, If I was to place the range on the LEFT JOIN table example above of -10 to 0, and 0 to 30, the final table would look like this:

Trange    |count|
-----------------
-10 - 0   | 6   |
0 - 30    | 3   |

This above transformation is where I am stumped and I fear to make my life simpler I just need to create one big table instead...

Thanks in advance

have you search group by range, [here](https://stackoverflow.com/a/233223/2996989) or you dont have defined bins , — Ahmed Sunny, Mar 25 '20 at 19:11
Does this answer your question? [In SQL, how can you "group by" in ranges?](https://stackoverflow.com/questions/232387/in-sql-how-can-you-group-by-in-ranges) — Ahmed Sunny, Mar 25 '20 at 19:12
Have you attempted anything on binning those data? Your query there doesn't show any binning code. — Eric, Mar 25 '20 at 19:25
Consider handling issues of data display in application code — Strawberry, Mar 25 '20 at 19:29

Eugen Rieck · Accepted Answer · 2020-03-25T20:08:06.587

0

You were very close! Just start with your existing query and then wrap it.

For your bins use another lookup table if you don't want to be bound to a fixed interval:

bins:

mintemp | maxtemp
 -10    | 0
   0    | 30

I will use num instead of count, as I never use reserved words as columns:

SELECT 
  CONCAT(mintemp, ' - ', maxtemp) AS Trange,
  SUM(baseview.num) AS num
FROM bins
INNER JOIN (
  SELECT
    t1.avg_temperature as T_aver,
    t2.new_confirmed as num
  FROM 3_day_avg as t1
  LEFT JOIN table2 as t2 on t1.date = t2.date
  ) AS baseview
ON baseview.T_aver>bins.mintemp AND baseview.T_aver<=bins.maxtemp
GROUP BY bins.mintemp;

edited Mar 25 '20 at 20:08

answered Mar 25 '20 at 19:10

Eugen Rieck

64,175
10
70
92

I am trying your answer and I get this as the error; Unknown column 'baseview.mintemp' in 'group statement' – NRav Mar 25 '20 at 19:57
Good catch: Typo in the GROUP BY ... fixed it. – Eugen Rieck Mar 25 '20 at 20:08
It actually needs to be GROUP BY Trange ... for me that worked flawlessly. Thanks for the help! – NRav Mar 26 '20 at 10:44

score 0 · Answer 2 · answered Mar 25 '20 at 19:13

SELECT ranges.caption Trange,
       SUM(t2.new_confirmed) as `count` 
FROM 3_day_avg as t1 
LEFT JOIN table2 as t2 on t1.date = t2.date
JOIN ( SELECT -10 t_from, 0 t_to, '-10 - 0' caption 
       UNION ALL
       SELECT 0, 30, '0 - 30' ) ranges ON t1.avg_temperature >= ranges.t_from 
                                      AND t1.avg_temperature < ranges.t_to 
GROUP BY ranges.caption;

It is better to create static ranges table instead of dynamically generated. This allows to create and to store a lot of pre-defined sets of ranges.

score 0 · Answer 3 · answered Mar 25 '20 at 19:20

You can group by a CASE expression which contains your bins:

SELECT
  CASE 
    WHEN t1.avg_temperature >= -10 and t1.avg_temperature <= 0 THEN '-10 - 0'
    WHEN t1.avg_temperature > 0 and t1.avg_temperature <=30 THEN '0 - 30'
  END AS Trange,
  SUM(t2.new_confirmed) AS count 
FROM 3_day_avg AS t1 LEFT JOIN table2 AS t2 
ON t1.date = t2.date
GROUP BY Trange

You may add more bins in the CASE expression, change the ranges and the inequality signs to suit your requirement.

mysql LEFT JOIN followed by GROUP BY with BETWEEN Ranges

3 Answers3