MySQL Error: SELECT list is not in GROUP BY clause

group-by, mysql, mysql-error-1055

Solution

When you use GROUP BY, you can use expressions in your select-list only if they have a single value per group. Otherwise you get ambiguous query results.

In your case, MySQL believes that `s.status` might have multiple values per group. For example, you're grouping by `p.products_id` but `s.status` is a column in another table `specials`, perhaps in a one-to-many relationship with table `products`. So there might be multiple rows in `specials` with the same `products_id`, but different values for `status`. If that's the case, which value for `status` should the query use? It's ambiguous.

In your data, you might happen to limit the rows such that you only have one row in `specials` for each row in `products`. But MySQL can't make that assumption.

MySQL 5.6 and earlier let you write such ambiguous queries, trusting that you know what you're doing. But MySQL 5.7 enables more strict enforcement by default (this can be made less strict to behave like earlier versions).

The fix is to follow this rule: Every column in your select-list must fall into one of three cases:

- The column is inside an aggregate function like COUNT(), SUM(), MIN, MAX(), AVERAGE(), or GROUP_CONCAT().

- The column is one of the column(s) named in the `GROUP BY` clause.

- The column is functionally dependent on the column(s) named in the `GROUP BY` clause.

For more explanation read this excellent blog: Debunking GROUP BY myths

Re your comment, I can only make a guess because you have not posted your table definitions.

I'm guessing that `products_description` and `manufacturers` are functionally dependent on `products`, so it's okay to list them as is in the select-list. But this assumption may not be correct, I don't know your schema.

Anyway, the error about `s.status` should be resolved by using an aggregate function. I'm using `MAX()` as an example.

SELECT p.*,
pd.*,
m.*,
MAX(IF(s.status, s.specials_new_products_price, NULL)) 
  AS specials_new_products_price,
MAX(IF(s.status, s.specials_new_products_price, p.products_price)) 
  AS final_price
FROM products p 
LEFT OUTER JOIN specials s ON p.products_id = s.products_id  
INNER JOIN manufacturers m ON p.manufacturers_id = m.manufacturers_id
INNER JOIN products_description pd ON p.products_id = pd.products_id
INNER JOIN products_to_categories p2c ON p.products_id = p2c.products_id
INNER JOIN categories c ON p2c.categories_id = c.categories_id
WHERE p.products_view = 1  
AND p.products_status = 1
AND p.products_archive = 0
AND c.virtual_categories = 0
AND pd.language_id = 1
GROUP BY p.products_id;

I also rewrote your joins in the proper way. Comma-style joins should be avoided.

Problem

Original source

Related problems

• MySQL: Select DISTINCT / UNIQUE, but return all columns?