Non Group by column in select working in one machine and not in another

Question

I have two machines and I am trying to run the below-mentioned query in both of them.

SELECT 
    bdm.brand_id AS brandId,
    bdm.brand_name AS brandName,
    fse.seller_code AS dummySeller,
    bdm.feed_source AS feedSource
FROM
    `brand_distributor_mapping` bdm
        JOIN
    `feed_source` fse ON bdm.feed_source = fse.name
GROUP BY bdm.brand_id ,bdm.feed_source;

It's working on one machine and giving Error code 1055 in another.

Mysql version of both machine:

Not working - mysql Ver 14.14 Distrib 5.6.19, for Linux (x86_64) using EditLine wrapper.
Working - mysql Ver 14.14 Distrib 5.5.53, for debian-linux-gnu (x86_64) using readline 6.3

The columns `brand_name` and `seller_code` either need to be in an aggregate function, or you could use a subquery to determine values for them. — Tim Biegeleisen, Jan 27 '17 at 07:16
Please update your question with sample data and expected output. — Tim Biegeleisen, Jan 27 '17 at 07:16
Possible duplicate of [Is there ANY\_VALUE capability for mysql 5.6?](http://stackoverflow.com/questions/37089347/is-there-any-value-capability-for-mysql-5-6) — e4c5, Jan 27 '17 at 07:19
As mentioned by Ravinder Reddy, it is because of SQL_MODE=ONLY_FULL_GROUP_BY. Thanks guys. — Napstablook, Jan 27 '17 at 07:26

score 2 · Accepted Answer · answered Jan 27 '17 at 07:19

2

It must be due to SQL_MODE set to ONLY_FULL_GROUP_BY by default.

Better you always practice a full group by aggregation in your queries. Otherwise though MySQL accepts and retrieves results based on the SQL_MODE set, they may not be correct.

You may want to change your group by clause like below:

GROUP BY
    bdm.brand_id,
    bdm.brand_name,
    fse.seller_code,
    bdm.feed_source

Refer to: MySQL Documentation on ONLY_FULL_GROUP_BY

answered Jan 27 '17 at 07:19

Ravinder Reddy

23,692
6
52
82

That must be it. I'll try to fetch data using inner query. Thanks. – Napstablook Jan 27 '17 at 07:24

Non Group by column in select working in one machine and not in another

1 Answers1