MySQL Error: SELECT list is not in GROUP BY clause -

i have problem query , mysql throws following error:

#1055 - expression #66 of select list not in group clause ,  contains nonaggregated column 's.status' not functionally  dependent on columns in group clause; incompatible  sql_mode=only_full_group_by 

query is:

select   p.*, pd.*, m.*, if(s.status, s.specials_new_products_price, null) specials_new_products_price, if(s.status, s.specials_new_products_price, p.products_price) final_price products p  left join specials s on p.products_id = s.products_id   left join manufacturers m using(manufacturers_id) ,            products_description pd,           categories c,           products_to_categories p2c p.products_view = 1   , p.products_status = 1 , p.products_archive = 0 , c.virtual_categories = 0 , p.products_id = pd.products_id , p.products_id = p2c.products_id , p2c.categories_id = c.categories_id , pd.language_id = 1  group p.products_id; 

when use group by, can use expressions in select-list if have single value per group. otherwise ambiguous query results.

in case, mysql believes s.status might have multiple values per group. example, you're grouping p.products_id s.status column in table specials, perhaps in one-to-many relationship table products. there might multiple rows in specials same products_id, different values status. if that's case, value status should query use? it's ambiguous.

in data, might happen limit rows such have 1 row in specials each row in products. mysql can't make assumption.

mysql 5.6 , earlier let write such ambiguous queries, trusting know you're doing. mysql 5.7 enables more strict enforcement default (this can made less strict behave earlier versions).

the fix follow rule: every column in select-list must fall 1 of 3 cases:

• the column inside aggregate function count(), sum(), min, max(), average(), or group_concat().
• the column 1 of column(s) named in group by clause.
• the column functionally dependent on column(s) named in group by clause.

for more explanation read excellent blog: debunking group myths

---

re comment, can make guess because have not posted table definitions.

i'm guessing products_description , manufacturers functionally dependent on products, it's okay list them in select-list. assumption may not correct, don't know schema.

anyway, error s.status should resolved using aggregate function. i'm using max() example.

select p.*, pd.*, m.*, max(if(s.status, s.specials_new_products_price, null))    specials_new_products_price, max(if(s.status, s.specials_new_products_price, p.products_price))    final_price 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 p.products_view = 1   , p.products_status = 1 , p.products_archive = 0 , c.virtual_categories = 0 , pd.language_id = 1 group p.products_id; 

i rewrote joins in proper way. comma-style joins should avoided.