MySQL Handling of GROUP BY. Getting ONLY_FULL_GROUP_BY error

Question

EDIT: This has been identified as possible duplicate of question #1055 - Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column this is incompatible with sql_mode=only_full_group_by

This is incorrect due to the answer being to change the global sql_mode settings. MySQL changed the ONLY_FULL_GROUP_BY default for good reason based on functional dependencies and returning arbitrary data

My question and answer give a short but accurate explaination on the error and how to circumvent it using more verbose statements.

Thanks for reading, the table is as follows:

+-----------+-------------+-----------+----------+-------+
| productID | productCode | name      | quantity | price |
+-----------+-------------+-----------+----------+-------+
|      1001 | PEN         | Pen Red   |     5000 |  1.23 |
|      1002 | PEN         | Pen Blue  |     8000 |  1.25 |
|      1003 | PEN         | Pen Black |     2000 |  1.25 |
|      1004 | PEC         | Pencil 2B |    10000 |  0.48 |
|      1005 | PEC         | Pencil 2H |     8000 |  0.49 |
+-----------+-------------+-----------+----------+-------+

So if I run the command:

SELECT * FROM products GROUP BY productCode;

I get the following error:

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'southwind.products.productID' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

So from what I have gathered reading the mysql documentation, since there are duplicate values in my GROUP BY argument it throws an error, refusing to return something arbitrarily. I would rather not disable this so any help understanding what I am missing to make the query work is much appreciated.

Thank you.

Answer 1

based on your mode, you just do like this:

SELECT Max(price) FROM products GROUP BY productCode;

You see that error because, when you group by productCode it means the first three records together and the last two one together, And because you have used select * you want to show all fields. So the problem is you have three different productID not just one if you want to have them too you need to use GROUP_CONCAT() like this for all.

SELECT GROUP_CONCAT(`productID ` separator ',') as ids , Max(price) FROM products GROUP BY productCode;

So you concat all IDs as a string.

Answer 2

Figured out how to get past the error. Within the table above there are duplicate values in productCode. MySQL isn't given an explicit row to grab when I ask it to GROUP BY so it throws an error. It wants me to tell it in the SELECT arguments exactly what column values to grab.

now using aggregate functions in my SELECT arguments as follows:

SELECT COUNT(productID),productCode, MAX(name), MAX(quantity), MAX(price) FROM products GROUP BY productCode;

I get a result set as follows:

+------------------+-------------+-----------+---------------+------------+
| COUNT(productID) | productCode | MAX(name) | MAX(quantity) | MAX(price) |
+------------------+-------------+-----------+---------------+------------+
|                2 | PEC         | Pencil 2H |         10000 |       0.49 |
|                3 | PEN         | Pen Red   |          8000 |       1.25 |
+------------------+-------------+-----------+---------------+------------+

To be clear these are not actual rows in the table since it finds COUNT in productID to give me how many rows there are of each unique productCode "group." Also it is looking for the MAX values of the columns.

If I wanted to get actual unique rows based on the productCode, I could using these two statements:

SELECT MIN(productID), productCode FROM products GROUP BY productCode;
+----------------+-------------+
| MIN(productID) | productCode |
+----------------+-------------+
|           1004 | PEC         |
|           1001 | PEN         |
+----------------+-------------+

then:

SELECT * FROM products WHERE productID = 1004 OR productID = 1001;

Giving me:

+-----------+-------------+-----------+----------+-------+
| productID | productCode | name      | quantity | price |
+-----------+-------------+-----------+----------+-------+
|      1001 | PEN         | Pen Red   |     5000 |  1.23 |
|      1004 | PEC         | Pencil 2B |    10000 |  0.48 |
+-----------+-------------+-----------+----------+-------+

So again you have to be explicit about the other columns. If anyone wants to add to this in the comments please do. Thanks.