Найти MAX (среднее значение)) после группировки по двум полям

#mysql #sql #ms-access

Вопрос:

Имя таблицы c_list .

 Country   City      Rating   Date
------------------------------------
France    Brest     95       24092016       
France    Brest     98       27092016
France    Brest     95       03102016
France    Lille     100      26092016     
France    Lille     92       28092016
Japan     Tokyo     98       02102016

Существует более 50 разных стран, и в каждой стране несколько городов. И в каждом городе может быть более одной строки записи или более. Я хочу выбрать один город с самым высоким средним рейтингом (сравнить с городами в своей стране), а затем сравнить со всеми другими городами в разных странах. Итак, в конечном запросе должны отображаться все страны и их ОДИН город с max (среднее значение (рейтинг)) и в порядке убывания. Пример вывода:

 Country   City        max(avg(rating))   
-------------------------------------
 USA      New York    97.25
 UK       Cardiff     96.70
 Germany  Greven      96.50
 Turkey   Afyon       94.88
 France   Guipavas    94.10
 Canada   Cartwright  91.35

Я могу получить только max (среднее значение (рейтинг)) для одной страны. Нужна помощь.

 SELECT top 1 country, city, Avg(rating) AS Ratings
FROM c_list
where country = 'France'
GROUP BY city, country
order by Ratings desc

(Отредактировано) Результат, который я хочу, похож на конкурс Мисс мира. Соревнуйтесь и выиграйте у местного участника в вашей стране первым. Следующий (мой окончательный набор результатов) — соревноваться с победителями из других стран и ранжировать их с первого по последний, используя их среднее значение (рейтинг), которое они получили лучше в своей стране.

1. Это для Ms-access или Mysql

2. Я не знаком с синтаксисом ms-access, поэтому я не буду пытаться писать SQL, но попробуйте разделить ваши запросы на две части.. Один находит, какой город имеет самый высокий средний рейтинг, в то время как другой присоединяется к этому списку городов со странами. Попробуйте этот подход

3. я использую ms-access

Ответ №1:

Если я не ошибаюсь, вы ищете это

 SELECT country, 
       city, 
       Avg(rating) AS Ratings 
FROM   c_list A 
GROUP  BY city, 
          country 
HAVING Avg(rating) = (SELECT TOP 1 Avg(rating) AS Ratings 
                      FROM   c_list B 
                      WHERE  a.country = b.country 
                      GROUP  BY city 
                      ORDER  BY ratings DESC) 
ORDER  BY ratings DESC

Примечание: Если вы используете Mysql TOP ключевое слово replace с LIMIT

1. это не только Франция. я хочу получить ответ для всех стран и их городов с наивысшим рейтингом.

2. @Yithirash — Удалить Where предложение France from . Обновлена проверка ответа сейчас ‘

Ответ №2:

Базовая таблица:

выберите t.* из temp_lax t СТРАНА ГОРОД РЕЙТИНГ 1 Франция Брест 95 2 Франция Брест 98 3 Франция Брест 95 4 Франция Лилль 100 5 Франция Лилль 92 6 Япония Токио 98

Запрос:

выберите t1.country, t1.city, средний (t1.Рейтинг) рейтинг из temp_lax t1 group по t1.country, t1.city, имеющий среднее (t1.рейтинг) = (выберите максимальное (среднее (рейтинг)) из temp_lax t2, ГДЕ t1.country = t2.country GROUP ПО t2.city) порядок по рейтингу desc

ВЫВОД: РЕЙТИНГ СТРАНЫ ГОРОДА 1 Япония Токио 98 2 Франция Лилль 96 3 Франция Брест 96

Пожалуйста, дайте мне знать, если вы ищете другой набор результатов.