Geek asks and answers

SQL query with middle subquery

I am making a SQL query to the weather database, I need wind_direction and windspeed.
This is my currect request:

SELECT wind_direction,
       windspeed
  FROM weather
  WHERE time >= curdate() and
        time < (curdate() + interval 1 day) AND
        windspeed > 0
  ORDER BY wind_direction ASC

This will delete all values ​​where windspeed = 0, and will only show data for today.

Request Output:

wind_direction   windspeed  
0               10.1  
0               11.2  
23              7.6  
23              1.4

As you can see, I get duplicate values ​​that are understandable, but my graphic display system does not support this, it does not know what value to use.
I need one unique wind_direction and avg () windspeed for this direction.

+3
source share
1 answer
SELECT  wind_direction, AVG(windspeed)
FROM    weather
WHERE   time >= curdate() and time < (curdate() + interval 1 day)
        AND windspeed > 0
GROUP BY
        wind_direction
ORDER BY
        wind_direction ASC

If you need one wind_direction, say, with maximum average speed, use this:

SELECT  wind_direction, AVG(windspeed) AS avg_speed
FROM    weather
WHERE   time >= curdate() and time < (curdate() + interval 1 day)
        AND windspeed > 0
GROUP BY
        wind_direction
ORDER BY
        avg_speed DESC 
LIMIT 1
+5
source

More articles:


All Articles