Comments on: Solve Query Failures Regarding ONLY_FULL_GROUP_BY SQL Mode

By: Sašo Kovačič

Sašo Kovačič — Mon, 25 Nov 2019 20:24:29 +0000

Sorry for the long question. I see I have to refresh my knowledge about SQL. Using ORM tools for too long can be harmful. Slowly you forget about the very basic rules of the SQL language… Answer to my question is to put all the non-aggregate columns into the group by statement.

By: Sašo Kovačič

Sašo Kovačič — Mon, 25 Nov 2019 19:05:24 +0000

I need to migrate to the 5,7 version of mysql server and I have some problems with queries that selects fields that are not in the group by statement.

Here is an example of one of those queries. It is really simple one but still…

I have two tables: risks and measures
Every risk can have 0 or more measures.

Here is a structure of the risks table

id
name
residual_direct_cost
residual_indirect_cost
etc…

and here is a structure of the measures table

id
risk_id
name
total_annual_cost
etc…

and here is a query that I’m executing in our application

SELECT risks.id, risks.name, risks.residual_direct_cost, risks.indirect_residual_cost, SUM(measures.total_annual_cost) AS measures_cost
FROM risks
INNER JOIN measures ON measures.risk_id = risks.id
ORDER BY risks.name
GROUP BY risks.id

What I want to get is a list of risks with the total cost of their measures…

id | name | residual_direct_cost | residual_indirect_cost | measures_cost
1 | Risk 1 | 3000.00 | 3400.00 | 432.00
2 | Risk 2 | 4320.00 | 1234.00 | 542.00
etc..

This query is not valid in mysql 5.7+. So how can I rewrite it to be valid. I can’t just group by the name or *_cost fields, because there can be more than one risk with the same value in each of these fields.

Thanks

By: Vijay Kumar Kanta

Vijay Kumar Kanta — Tue, 15 Oct 2019 07:16:22 +0000

Good post. Thanks for clarifying with potential fixes for SQL compliance using your own example code. Well done.

By: Tony Marston

Tony Marston — Tue, 14 May 2019 09:00:10 +0000

I think that restricting yourself to the SQL-92 standard is too, er, restrictive. The SQL-99 standard introduced the idea of “functional dependency” which means that if the GROUP BY clause contains a key field then any columns which are functionally dependent on that key field (i.e. on the same record as the one with that key) can appear in the SELECT list without having to appear in the GROUP BY list.

By: Jean-François Gagné

Jean-François Gagné — Mon, 13 May 2019 16:48:49 +0000

Other post on the same subject:
https://gabi.dev/2016/03/03/group-by-are-you-sure-you-know-it/