Как создать правильный SQL-запрос с группировкой и условием?

Question

[Андрей Титов]

Есть таблицы, из которых нужно выполнить запрос с группировкой и условием:

SELECT
    l.id,
    c.name,
    l.title,
    l.image_path,
    l.start_price,
    MAX(b.current_price),
    COUNT(b.lot_id)
FROM
    lots l
LEFT JOIN bets b ON
    l.id = b.user_id
JOIN categories c ON
    l.category_id = c.id
WHERE
    NOW() BETWEEN l.created_date AND end_lot_date AND lot_id = 1
GROUP BY
    b.lot_id
ORDER BY
    l.created_date
DESC

При выполнении выходит ошибка:

#1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'yeticave.l.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Что подправить в запросе и из-за чего может быть ошибка?

Comments

[alexalexes]

В group by должны быть добавлены выбираемые поля, которые не входят в аналитические функции max и count, это как минимум:

group by b.lot_id,
    l.id,
    c.name,
    l.title,
    l.image_path,
    l.start_price

Добавили что-то в select необернутое в аналитику, и тут же это нужно продублировать в group by. При наборе текста запроса - это работает на уровне рефлексов.

Answer 1

[Дмитрий Энтелис]

1. SELECT ... COUNT(b.lot_id) ... group by b.lot_id - всегда будет 1.
Вряд ли вам нужна группировка по lot_id.

2.

WHERE NOW() BETWEEN l.created_date AND end_lot_date

- не будет использовать индекс, лучше явно писать

WHERE l.created_date <= NOW()  AND end_lot_date >= NOW()

3. Когда вы пишите запрос с group by - любые выбираемые данные должны быть или явно указаны в group by, или обернуты какими-то аггрегирующими фунциями (count, avg, max, group_concat итд). Иначе SQL тупо не понимает что ему делать с этим.

Приведу пример для наглядности:

name  | age
Петя  | 10
Вася  | 10

select age, name from ... group by age.
В postgress/mssql/oracle это будет ошибка.
MySQLс дефолтными настройками проглатывает такой запрос, отдавая кортеж в котором age = 10, а name = случайное значение из подходящих name.
К счастью в MySQL есть замечательный параметр sql_mode который можно установить в only_full_group_by и все начнет работать как у взрослых. (что и произошло в вашем случае, судя по логу)

Comments

[Андрей Титов]

Дмитрий Энтелис - "GROUP BY b.lot_id" - разве не явное указание, по каким полям группировать? Или что то другое нужно указать?

[Дмитрий Энтелис]

Андрей Титов, я не знаю структуру ваших таблиц и что вы хотите получить, но в режиме телепата думаю что нужна группировка

group by  l.id, c.name, l.title, l.image_path, l.start_price

а в count должно быть что-то типа COUNT(b.bet_id)

Answer 2

[Rsa97]

Начиная с MySQL 5.7 используется строгий подход к GROUP BY. Все поля в запросе должны либо входить в список полей, по которым идёт группировка, либо быть в агрегатной функции.
Варианты решения:
- переписать запрос с учётом стандарта;
- использовать агрегатную функцию ANY_VALUE(), явно указав, что вам всё равно, какое из значений в объединяемых строках использовать;
- вернуть старый режим работы MySQL через системную переменную sql_mode.