Alb Alb - 4 months ago 9
SQL Question

MySQL Max Average grouped by 2 fields

I am trying to do the following. I have this table

x
:

id | rssi | Loc

160 | -83 |51
160 | -61 |51
161 | -80 |51
163 | -81 |51
161 | -88 |52
161 | -82 |53


I would like to get the maximum average grouped by id for each of the
Loc
. In other words, I want to do an average grouped by
Loc
,
id
and then from it getting the max
Loc
and its
id
for each
Loc
.
The result would be something like this:

id | maxAvg | Loc

160 | -72 | 51
163 | -81 | 52
161 | -82 | 53


I have tried it so far:

SELECT loc, id, avg(rssi) as w FROM x
group by loc, id


This first part is doing the average of rssi grouping per loc and id but later here is where I am a little bit lost:


Select Loc, id, Max(t.w) From

(SELECT t.Loc, t.id, avg (rssi) as
w FROM x group by
Loc, id) t

group by t.Loc

order by t.Loc asc;


I don't know if it is the right approach. Any help would be more than welcome.
Thanks.

Answer

This is tricky!! Let's walk through it.You can follow along in this example.

You are getting towards where you need to be. The difficult part is bringing in the id that matches the average that you already calculated.

So the first thing I did was order the results, so that the lowest average appears as the first row of each group:

SELECT loc, id, avg(rssi) as w
FROM p
group by loc, id order by loc, w desc;

From there I made a quick counter to rank each row, and pulled only the rank =1

SET @loc = NULL, @currvalue = NULL;

select  id, w,
    @currcount := IF(@loc = loc, @currcount + 1, 1) AS rank,
    @loc := loc AS loc from 
(SELECT loc, id, avg(rssi) as w
FROM p
group by loc, id order by loc, w desc) r
having rank = 1
;

This gives you the desired results

Comments