How to resolve SELECT list is not in GROUP BY clause and contains nonaggregated?

Question

I am getting an error with MYSQL 5.7 on this request. How to resolve this error ?

#1055 - Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.c.customers_group_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

select  SQL_CALC_FOUND_ROWS  c.customers_firstname, 
                             c.customers_lastname, 
                             c.customers_group_id,
                             sum(op.products_quantity * op.final_price) as ordersum 
from customers c,
     orders_products op,
     orders o
where c.customers_id = o.customers_id 
and o.orders_id = op.orders_id 
group by c.customers_firstname, 
         c.customers_lastname 
order by ordersum DESC

Answer 1

include c.customers_group_id also in the group by clause

Answer 2

You missed c.customers_group_id in the GROUP BY clause.

Instead the old-style comma-separated list of tables pattern you can use the ANSI JOIN pattern.

The below code will work in your case:

SELECT  SQL_CALC_FOUND_ROWS  c.customers_firstname, 
                             c.customers_lastname, 
                             c.customers_group_id,
                             sum(op.products_quantity * op.final_price) as ordersum 
FROM customers c
JOIN orders o ON o.customers_id = c.customers_id 
JOIN orders_products op ON op.orders_id = o.orders_id
GROUP BY c.customers_firstname, 
         c.customers_lastname,
         c.customers_group_id -- you missed this
ORDER BY ordersum DESC