How to create proper SQL query with grouping and condition?

Question

MySQL

There are tables from which you need to execute a query with grouping and a condition:

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

When executed, an error occurs:

#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

What to correct in the request and what could be causing the error?

Answer 1

1. SELECT ... COUNT(b.lot_id) ... group by b.lot_id- will always be 1.
It is unlikely that you need grouping by lot_id.
2.

WHERE NOW() BETWEEN l.created_date AND end_lot_date

- won't use index, it's better to write explicitly

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

3. When you write a query with a group by - any selected data must be either explicitly specified in the group by, or wrapped in some kind of aggregating functions (count, avg, max, group_concat, etc.). Otherwise, SQL stupidly does not understand what to do with it.
I will give an example for clarity:

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

select age, name from ... group by age.
In postgress/mssql/oracle this will be an error.
MySQL with default settings swallows such a query, returning a tuple in which age = 10 and name = a random value from the matching names.
Fortunately, MySQL has a wonderful sql_mode parameter that can be set to only_full_group_by and everything will start working like adults. (which happened in your case, judging by the log)

Answer 2

Since MySQL 5.7, a strict GROUP BY approach has been used. All fields in the query must either be included in the list of fields to be grouped by, or be in the aggregate function.
Solutions:
- rewrite the query in accordance with the standard;
- use the aggregate function ANY_VALUE() , explicitly indicating that you don't care which of the values ​​in the combined rows to use;
- return the old mode of operation of MySQL through the system variable sql_mode .