Как сделать выборку фотографий из БД у которых более 3-х оценок?

[verfaa]

Итак, есть таблица top100_votes, в которой хранятся оценки пользователей за фотографии (похоже на аналог топ-100 мамбы, лавплэнет и т.д.). Она имеет такой вид:

id | id_foto | id_voter | mark | data

id - id записи, auto_increment
id_foto - айди фотографии за которую проголосовали
id_voter - айди пользователя, который проголосовал за фото
mark - оценка, которую он поставил, от 1 до 6
data - время голосования

Задача:
Нужно сделать выборку фотографий у которых есть минимум 5 оценок, рассчитать среднюю оценку (т.е. сумму оценок разделить на количество голосов) и отсортировать в порядке убывания.
Помогите написать SQL запрос пожалуйста, БД MySQL, тип таблицы MyISAM

 

[esche]

Так примерно:

SELECT id_foto, COUNT(id_voter) as cnt, AVG(mark) as mark_avg
FROM `top100_votes`
GROUP by id_foto
HAVING COUNT(id_voter) >= 5
ORDER BY mark_avg DESC

 

[verfaa]

Спасибо Для просмотра ссылки Войди или Зарегистрируйся! Я добавил FROM tbl_name и все работает.
А теперь вопрос намного сложнее:
Есть ещё одна таблица fotos хранящая информацию о фотографиях юзеров, в ней кроме других полей есть поля:
id_foto - айди фотографии
id_user - айди пользователя, владельца фотографии

И ещё одна таблица users с полями:
id_user - айди пользователя
gender - пол пользователя
country - страна пользователя

Допустим в скрипте у нас уже имеется пол и страна пользователя, например:

$country_id = 251;
$gender = 2;

Помогите сделать выборку фотографий из таблицы top100_votes, принадлежащих пользователям из страны $country_id, пола $gender у которых есть минимум 5 оценок, рассчитать среднюю оценку (т.е. сумму оценок разделить на количество голосов) и отсортировать в порядке убывания.

 

[verfaa]

Написал SQL запрос, который вроде работает правильно, но при тестировании обнаружил, что функция AVG считает среднее число неправильно!
Вот такой SQL запрос у меня получился:

SELECT a.id_foto, e.id_user AS id_owner, s.gender, s.id_country, COUNT(a.id_user) as cnt, AVG(a.mark) AS mark_avg
FROM top100_votes a
JOIN user_upload e ON a.id_foto=e.id
LEFT JOIN user s ON e.id_user=s.id
WHERE s.id_country = '44'
GROUP by a.id_foto
HAVING COUNT(a.id_user) >= 2
ORDER BY mark_avg DESC

Например для фото с ID 34950 в БД имеются следующие оценки:

Но для фото ID 34950 скрипт выводит среднюю оценку 4.5 хотя, если посчитать средняя оценка 3.5
Для других фото бывает выводит и 7, хотя максимальная оценка, которую можно поставить 6
Помогите найти ошибку или переписать SQL запрос правильно.
Тип поля mark - ENUM с возможными значениями 'skip','1','2','3','4','5','6'

 

[Prometeus]

Возможных причин может быть несколько. Например, ограничение по пользователям только одной страны s.id_country = '44'.
Или вот этот момент проверьте user_upload.id = top100_votes.id_foto, нам отсюда не видна структура таблиц и корретность связей.
Приведите результат выполнения такого запроса, тогда всё можно будет расставить по своим местам:

SELECT a.id_foto, e.id_user AS id_owner, s.gender, s.id_country, a.id_user, a.mark AS mark_avg
FROM top100_votes a
JOIN user_upload e ON a.id_foto=e.id -- user_upload.id = top100_votes.id_foto ?
LEFT JOIN user s ON e.id_user=s.id
WHERE s.id_country = '44' and a.id_foto = 34950

 

[verfaa]

окей спасибо, приду вечером домой и попробую.
Кстати, вчера, в качестве отладки решил вывести значения COUNT(a.mark) и SUM(a.mark)
И что интересно, COUNT(a.mark) вывело правильно - 6, а вот SUM(a.mark) - неправильно, что-то ок 30-33.

 

[Шёпот]

COUNT - количество чисел, SUM - сумма этих чисел

 

[Prometeus]

И? Скриншот видишь? Почему 30-33 вместо 21?

 

[Шёпот]

И? Скриншот видишь? Почему 30-33 вместо 21?

И? Может в момент теста использовались другие данные, не?

 

[verfaa]

проблема уже решилась давно, а заключалась она в том, что использовался тип поля mark - ENUM
У меня изначально были сомнения по поводу типа поля, поэтому в теме я указывал, что тип поля mark - ENUM
На sql.ru сразу верный ответ дали:

о, да ты вычисляешь среднее значение из строк - круто, там и не тока 7 может получиться

странно что тут никто не догадался, обычно самые толковые ответы дают именно на нулледе)
Сделал новую таблицу, для поля mark выставил TINYINT, а если юзер пропускает фото просто заношу в БД 0.
И все отлично стало работать.