SQL - return rows for values with MAX COUNT

Question

I want to access a weather table and sumamrise it in terms of days and months. I want some of the values to be AVG and some to be SUM.

I want to underpin the resulting record with values from the collective data that represent the maximum count but after a few combinations, I have not managed it.

EXAMPLE DATA:

day_date                main_weather     temp
2012-01-01 07:00:00     Cloudy           8.0
2012-01-01 08:00:00     Cloudy           10.0
2012-01-01 09:00:00     Sunny            12.0
2012-01-01 10:00:00     Sunny            16.0
2012-01-01 11:00:00     Sunny            18.0

WANTED RESULT:

DATE(day_date)          MAX(COUNT(main_weather)     AVG(temp)
2012-01-01              Sunny                       12.8

Here's my first SQL to show what I am trying to do:

SELECT 
    DATE(`day_date`), 
    MAX(COUNT(`main_weather`)),    <--- this is the piece I am stuck with the max values.
    AVG(`temp`) 
FROM `sma_weather`
GROUP BY `day_date`;

Well. Have you tried removing LIMIT 1 from your query? That is limiting the result to one row max. — Riz, Sep 26 '14 at 07:04
You'll need to get a COUNT() for each group and then determine the max count in an outer query or in your application logic. — Mike D., Sep 26 '14 at 07:11
Hi Mike D. Thanks I think I understand your suggestion. I have updated the question to make it easier to see what I am trying to achieve. — TheRealPapa, Sep 26 '14 at 07:46
Hi @Used_By_Already, I was replying to your answer as you deleted it! I am trying to get "average" weather for a period (day in this instance). My table contains hourly entries for a local. So I am counting all weather types for the period (day) then showing the weather type with the MAX count (in terms of row appearances).In your e.g.: the answer would be "Hot" — TheRealPapa, Sep 26 '14 at 08:00
as I was working through that explanation I understood what you are seeking - apologies again — Paul Maxwell, Sep 26 '14 at 08:02
You should read this link : http://stackoverflow.com/questions/2129693/mysql-using-limit-within-group-by-to-get-n-results-per-group. I didnt try it but it seems it could answer your question — Logar, Sep 26 '14 at 09:06

score 0 · Answer 1 · answered Sep 26 '14 at 07:06

For your second example, the query return only one row because you have added the option LIMIT 1. FOr the rest, I don't really understand your description of the result that you want to get so I cannot help you. It wouldn't hurt to give a litle example with a few lines of data so that people can understand what you are trying to achieve.

Paul Maxwell · Accepted Answer · 2014-09-26T08:19:27.220

0

Looks like a correlated subquery would do it, but not sure how well this will scale.

SELECT 
      DATE(`day_date`)    AS day
    , (
        SELECT  w1.`main_weather`
        FROM `weather` w1
        where DATE(w1.`day_date`) = DATE(`weather`.`day_date`) 
        GROUP BY
              DATE(`day_date`)
            , `main_weather`
        order by count(*) DESC
        limit 1
      )                   AS max_count_weather
    , AVG(`temp`)         AS av_temp
FROM `weather`
GROUP BY
      DATE(`day_date`)
;

See this SQLFiddle

edited Sep 26 '14 at 08:19

answered Sep 26 '14 at 07:55

Paul Maxwell

33,002
3
32
51

Hi @Used_By_Already. It works, but as you said it is very slow. I need to find a different way to peel the onion... and there's been plenty crying already! Thanks for the help! – TheRealPapa Sep 27 '14 at 01:16

score 0 · Answer 3 · answered Sep 26 '14 at 08:15

0

Try something like (untested):

select day_date, (select top 1 main_weather from MyTable t2 where date(t1.day_date) = date(t2.day_date) group by main_weather
order by count(*) desc) as Max_MainWeather, avg (temp) as AvgTemp
from MyTable t1
group by (day_date)

answered Sep 26 '14 at 08:15

SylvainL

3,926
3
20
24

Hi SylvainL thanks for the reply. MySQL complains about the "top 1" part of the suggestion – TheRealPapa Sep 26 '14 at 08:24
Sorry, this is for SQL-Server. Replace it with a LIMIT 1 at the end of the subquery. – SylvainL Sep 26 '14 at 08:26
Or use the other solution from Used_By_Already (you can drop the Date(da_date) from the Group By statement because they are all identical for each call to the subquery). – SylvainL Sep 26 '14 at 08:29

score 0 · Answer 4 · answered Sep 26 '14 at 08:32

As far as I understand your problem (as it was not easy for me to understand), I came up with the following solution:
select t.on_date, t.main_weather, avg(temp) from
(select date(day_time) as on_date, main_weather, avg(temp) as avgtemp
from weather group by on_date,main_weather order by main_weather desc limit 1
) as t join weather on date(day_time)=t.on_date group by t.on_date;

The query is tested and works fine for me.

SQL - return rows for values with MAX COUNT

4 Answers4