I'm getting MYSQ only_full_group_by Error

Question

I have a problem with my query and mysql throws the following error:

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

QUERY:

SELECT a.item_name,a.discounted_price,                 
               SUM(qty) as total,
               b.date_created
               FROM
               mt_view_order_details as a
               left join mt_order as b
               ON
               a.order_id=b.order_id
               WHERE
               b.date_created BETWEEN '2021-07-03 00:00:00' AND '2021-08-02 23:00:59'
               AND a.merchant_id = '3'
               AND a.status NOT IN ('initial_order')
               GROUP BY item_id
               ORDER BY item_name ASC

I'm using a shared hosting, so i can't change sql_mode, please tell me is there any other solution, Thanks in advance

score 0 · Accepted Answer · answered Aug 02 '21 at 16:26

With sql_mode=only_full_group_byevery column, that is not in the GROUP BY needs to have a aggregation function

So oyu could make

SELECT MIN(a.item_name),MIN(a.discounted_price),                 
               SUM(qty) as total,
               MIN(b.date_created)
               FROM
               mt_view_order_details as a
               left join mt_order as b
               ON
               a.order_id=b.order_id
               WHERE
               b.date_created BETWEEN '2021-07-03 00:00:00' AND '2021-08-02 23:00:59'
               AND a.merchant_id = '3'
               AND a.status NOT IN ('initial_order')
               GROUP BY item_id
               ORDER BY item_name ASC

or something lie this

 SELECT a.item_name,a.discounted_price,                 
           SUM(qty) as total,
           b.date_created
           FROM
           mt_view_order_details as a
           left join mt_order as b
           ON
           a.order_id=b.order_id
           WHERE
           b.date_created BETWEEN '2021-07-03 00:00:00' AND '2021-08-02 23:00:59'
           AND a.merchant_id = '3'
           AND a.status NOT IN ('initial_order')
           GROUP BY item_id,a.item_name,a.discounted_price,b.date_created
           ORDER BY item_name ASC

Thank you sir, it really helped :) – Sahibzada Jawad Aug 02 '21 at 16:35 — Sahibzada Jawad, Aug 02 '21 at 16:35

score -1 · Answer 2 · answered Aug 02 '21 at 16:26

The Error Says

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

First Expression is a.item_name. This means that there is a possibility that a single group may have more than 1 value for column a.item_name.

You can solve this in multiple ways:

If you are sure that the value in a.item_name will be unique for all values in a single group you can use any_value aggregate function.

SELECT any_value(a.item_name), a.discounted_price...

You can use other aggregate functions like group_concat, sum etc. if you want to process your data in some other way
You can disable sql mode only_full_group_by. Check this answer to disable -> Disable ONLY_FULL_GROUP_BY

I'm getting MYSQ only_full_group_by Error

2 Answers2