CODEWITHSUNDEEP
mysql
sandwood
sandwood

Reputation: 2167

How to group by and compute average on groups

I have the following simple wind table populated from an embedded device controlling a wind sensor.

describe wind;

Field        Type      Null   Key    Default    Extra
average      float     YES    NULL
min          float     YES    NULL
max          float     YES    NULL
direction    int(11)   YES    NULL
timestamp    datetime  YES    NULL

direction is in degree, so ranging from 0 to 360.

Typical entries : 

select * from wind order by timestamp desc limit 5;
average    min    max    direction    timestamp
7.34167    5.9    10.2    307        2018-04-26 10:24:07
6.655      4.2    8.9     301        2018-04-26 10:23:03
8.74667    7.2    10.8    307        2018-04-26 10:21:59
9.4925     7.4    10.7    295        2018-04-26 10:20:55
11.7175    8.6    14.2    306        2018-04-26 10:19:51

I want to compute the average wind distribution along the sixteen wind axes from N,NNE, and so on.

Based on this group by range in mysql I was able to make the grouping. (note that I can not use the 'floor' solution because of the North interval ranging from 348.25->360 AND 0->11.25 )

I think I am almost there, but I can not find the correct syntax to make the average (actually AVG(average) by Wind direction grouped).

Here is my current code : 

select w.dir as `wind direction`, count(*) as `occurence`
from (
    select CASE
    when direction BETWEEN 11.25 and 33.75 then 'NNE'
    when direction BETWEEN 33.75 and 56.25 then 'NE'
    when direction BETWEEN 56.25 and 78.75 then 'ENE'
    when direction BETWEEN 78.75 and 101.25 then 'E'
    when direction BETWEEN 101.25 and 123.75 then 'ESE'
    when direction BETWEEN 123.75 and 146.25 then 'SE'
    when direction BETWEEN 146.25 and 168.75 then 'SSE'
    when direction BETWEEN 168.75 and 191.25 then 'S'
    when direction BETWEEN 191.25 and 213.75 then 'SSW'
    when direction BETWEEN 213.75 and 236.25 then 'SW'
    when direction BETWEEN 236.25 and 258.75 then 'WSW'
    when direction BETWEEN 258.75 and 281.25 then 'W'
    when direction BETWEEN 281.25 and 303.75 then 'WNW'
    when direction BETWEEN 303.75 and 325.75 then 'NW'
    when direction BETWEEN 325.75 and 348.25 then 'NNW'
    else 'N'
     end as `dir`
    from wind) w
    group by w.dir

I tried adding

 AVG(w.average) as `mean`

on the first line but I am getting error #1054. I am unsure how to include the average column to be able to compute the AVG on it.

Bonus : I also have a secondary issue : the grouping is done by alphabetical order , as seen: 

wind direction    occurence
E                 31
ENE               58
ESE               66
N                 212
NE                128
NNE               62
NNW               326
NW                449
S                 108
SE                133
SSE               192
SSW               355
SW                47
W                 173
WNW               333
WSW               22

I would like to keep the order of my case statement (and ideally I woud like the North N case to be the first). 

order by w.dir

make the same thing, alphabetical sorting. How to keep the case order ?

Many thanks

Upvotes: 0

Views: 67

Answers (1)

Nick
Nick

Reputation: 147146

I think this query should give you the results you want (there's not enough sample data to be certain). Basically you just need to select average and direction in the subquery, then you can get the mean that you want, and you can order by the raw direction number (to which we add 11.75 and make it modulo 360 so that direction starts at 0 for N and goes up to 359 for NNW).

select w.dir as `wind direction`, round(avg(average),4) as mean, count(*) as `occurence`
from (
    select CASE
    when direction BETWEEN 11.25 and 33.75 then 'NNE'
    when direction BETWEEN 33.75 and 56.25 then 'NE'
    when direction BETWEEN 56.25 and 78.75 then 'ENE'
    when direction BETWEEN 78.75 and 101.25 then 'E'
    when direction BETWEEN 101.25 and 123.75 then 'ESE'
    when direction BETWEEN 123.75 and 146.25 then 'SE'
    when direction BETWEEN 146.25 and 168.75 then 'SSE'
    when direction BETWEEN 168.75 and 191.25 then 'S'
    when direction BETWEEN 191.25 and 213.75 then 'SSW'
    when direction BETWEEN 213.75 and 236.25 then 'SW'
    when direction BETWEEN 236.25 and 258.75 then 'WSW'
    when direction BETWEEN 258.75 and 281.25 then 'W'
    when direction BETWEEN 281.25 and 303.75 then 'WNW'
    when direction BETWEEN 303.75 and 325.75 then 'NW'
    when direction BETWEEN 325.75 and 348.25 then 'NNW'
    else 'N'
     end as `dir`,
    average,
    direction
    from wind) w
group by w.dir
order by floor(w.direction + 11.75) mod 360

Upvotes: 2

Related Questions