HAVING COUNT(name) = 1; 

The MySQL extension permits the use of an alias in the HAVING  clause for the aggregated column: 

SELECT name, COUNT(name) AS c FROM orders GROUP BY name HAVING c = 1; 

Standard SQL permits only column expressions in GROUP BY  clauses, so a statement such as this is invalid because FLOOR(value/100)  is a noncolumn expression: 

SELECT id, FLOOR(value/100) FROM tbl_name GROUP BY id, FLOOR(value/100); 

MySQL extends standard SQL to permit noncolumn expressions in GROUP BY  clauses and considers the preceding statement valid. 

Standard SQL also does not permit aliases in GROUP BY  clauses. MySQL extends standard SQL to permit aliases, so another way to write the query is as follows: 

SELECT id, FLOOR(value/100) AS val FROM tbl_name GROUP BY id, val; 

The alias val  is considered a column expression in the GROUP BY  clause. 

In the presence of a noncolumn expression in the GROUP BY  clause, MySQL recognizes equality between that expression and expressions in the select list. This means that with ONLY_FULL_GROUP_BY  SQL mode enabled, the query containing GROUP BY id, FLOOR(value/100)  is valid because that same FLOOR() expression occurs in the select list. However, MySQL does not try to recognize functional dependence on GROUP BY  noncolumn expressions, so the following query is invalid with ONLY_FULL_GROUP_BY enabled, even though the third selected expression is a simple formula of the id  column and the FLOOR() expression in the GROUP BY  clause: 

## 12.20.4 Detection of Functional Dependence 

SELECT id, FLOOR(value/100), id+FLOOR(value/100) FROM tbl_name GROUP BY id, FLOOR(value/100); 

A workaround is to use a derived table: 

SELECT id, F, id+F FROM (SELECT id, FLOOR(value/100) AS F FROM tbl_name GROUP BY id, FLOOR(value/100)) AS dt; 

The following discussion provides several examples of the ways in which MySQL detects functional dependencies. The examples use this notation: 

{ X } -> { Y } 

Understand this as “ X  uniquely determines Y ,”  which also means that Y  is functionally dependent on X . 

The examples use the world  database, which can be downloaded from https://dev.mysql.com/doc/index- other.html. You can find details on how to install the database on the same page. 

•Functional Dependencies Derived from Keys 

•Functional Dependencies Derived from Multiple-Column Keys and from Equalities 

