Question 263095 in PHP Versions Question posted in
You can find a list of all versions here.

Php versions – Updating GROUP BY queries for SQL mode ONLY_FULL_GROUP_BY

My hoster switches from MySQL 5.7 to 8.0.33 soon so ONLY_FULL_GROUP_BY is enabled by default then.

I can’t add ONLY_FULL_GROUP_BY already to the SQL mode for testing purposes right now because of missing rights. So it’s also not possible to just turn it off later when the hoster has updated the MySQL version.

So the only thing i can do now is to adjust the queries for working with ONLY_FULL_GROUP_BY later and they should work also right now if done correctly.

I’m an lazy coder doing stuff like this all the time:

SELECT *
FROM
table
WHERE
...
GROUP BY xyz
ORDER BY abc

Is it right, that i can’t use the * (=all columns) anymore if using GROUP BY in an SELECT query? 🙁

Example 1:

SELECT headhunter, COUNT(*) AS amount
FROM table
GROUP BY headhunter

Do i need to add amount here also to the GROUP BY?

Example 2:

SELECT *, departure, DATE_FORMAT(departure,'%d.%m.%y') AS departure2
FROM table
WHERE carnumber1=''
GROUP BY shipid
ORDER BY departure DESC

What happens here? I can’t use the * anymore here?

I need to remove the * and add shipid to the SELECT, also departure (departure2 too?) to GROUP BY and shipid to ORDER BY?

Also i have to look inside my PHP code which columns i process after this query in the while loop and add them all to the SELECT, GROUP BY and ORDER?!?

Example 3:

SELECT customer
FROM table
WHERE carnumber1!=''
GROUP BY customer
ORDER BY customer ASC

Here is no adjustment needed, or?

Example 4:

SELECT customer
FROM table
WHERE carnumber1!=''
AND preparation>date_sub(NOW(),INTERVAL 6 MONTH)
GROUP BY customer
ORDER BY customer ASC

Also no adjustment?

Example 5:

SELECT customer
FROM table
GROUP BY customer
HAVING MAX(preparation)<=date_sub(NOW(), INTERVAL 6 MONTH)
ORDER BY customer ASC

Is column preparation needed here (because of HAVING) in the SELECT, GROUP and ORDER?

Example 6:

SELECT DATE_FORMAT(delivery,'%M %Y'), COUNT(*) AS amount, SUM(purchase_price) AS summed
FROM table
GROUP BY MONTH(delivery), YEAR(delivery)

Just adding delivery to the SELECT and purchase_price to GROUP BY?

Thanks for all your hints, query fixings, explanations!

Tags:

2

Answers


  1. 0

    Here’s how I modify your queries for each example:

    Example 1:

    SELECT headhunter, COUNT(*) AS amount
FROM table
GROUP BY headhunter, amount;

    Example 2:

    SELECT shipid, departure, DATE_FORMAT(departure,'%d.%m.%y') AS departure2
FROM table
WHERE carnumber1=''
GROUP BY shipid, departure, departure2
ORDER BY shipid, departure DESC;

    Example 3:

    No adjustment needed as it's already compliant.

    Example 4:

    No adjustment needed; it's already compliant.

    Example 5:

    SELECT customer, MAX(preparation) AS max_preparation
FROM table
GROUP BY customer
HAVING max_preparation <= date_sub(NOW(), INTERVAL 6 MONTH)
ORDER BY customer ASC;

    Example 6:

    SELECT YEAR(delivery), MONTH(delivery), COUNT(*) AS amount, SUM(purchase_price) AS summed
FROM table
GROUP BY YEAR(delivery), MONTH(delivery);

    All the columns in the SELECT clause need to do is be either a part of the GROUP BY clause or use aggregate functions (like COUNT, SUM, or MAX). You can utilize aliases declared in the SELECT clause or use columns that are in the SELECT clause in the ORDER BY clause. These guidelines must be followed in order to make your queries compatible with ONLY_FULL_GROUP_BY mode.

    Login or Signup to reply.
  2. 0

    I’m an lazy coder doing stuff like this all the time:

    SELECT * FROM table WHERE

    Please, do not do that. In most cases it is wrong.

    See Why is SELECT * considered harmful? for further details.

    From MySQL Handling of GROUP BY

    If the ONLY_FULL_GROUP_BY SQL mode is enabled , MySQL rejects queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregate columns that are neither named in the GROUP BY clause nor are functionally dependent on them.

    In simple words the columns on the select list which are not part of an aggregate functions such as MIN(),MAX() etc should be part of the group by clause.

    An important thing to keep in mind. MySQL support Functional Dependencies, meaning if you use the primary key on the group by clause there is no need to add other columns because the primary key determines every attribute in the table.

    Consider a simple example which can be found on this fiddle as well.
    In the following example only the third query will fail because col2 is not part of the aggregate function nor part of the group by clause and the column used in the group by is not a primary key

    create table test (
  id int primary key,
  col1 int  ,
  col2 text );

insert into test values  (1,3,'Test1'),(2,2,'Test2'),(3,1,'Test3'),(4,1,'Test4');

    Query 1

    SELECT id,sum(col1),col2
FROM test
GROUP BY id;

    Query 2

    SELECT sum(col1),col2
FROM test
GROUP BY id;

    Query 3

    SELECT sum(col1),col2
FROM test
GROUP BY col1;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search