I have this query :

select p.id from product p 
            join product_variant pv on pv.product_id = p.id
            JOIN channel_pricing pr on pr.product_variant_id = pv.id
            WHERE p.id in (1,2,3,4,5) and p.main_taxon_id = 2
            GROUP BY pv.product_id
            ORDER BY pr.price ASC

I'm getting this error :

Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'sidratsoft_flora_db.pr.price' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Is there a way to fix it without changing the sql_mode?

  • For each Product id, you may have to apply the group functions like sum,avg etc on pr.price. – SQL.RK Feb 23 '17 at 14:29
  • 1
    Fix: change SELECT p.id to SELECT pv.product_id AS id and ORDER BY pr.price to ORDER BY MAX(pr.price) – ypercubeᵀᴹ Feb 23 '17 at 15:53
  • dbfiddle here – joanolo Jul 9 '17 at 9:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.