content_image/763608.jpg
## 12.20.3 MySQL Handling of GROUP BY 

<img src='content_image/763608.jpg'>

GROUP BY year WITH ROLLUP; 

SQL-92 and earlier does not permit queries for which the select list, HAVING  condition, or ORDER BY  list refer to nonaggregated columns that are not named in the GROUP BY  clause. For example, this query is illegal in standard SQL-92 because the nonaggregated name  column in the select list does not appear in the GROUP BY : 

SELECT o.custid, c.name, MAX(o.payment) FROM orders AS o, customers AS c WHERE o.custid = c.custid GROUP BY o.custid; 

For the query to be legal in SQL-92, the name  column must be omitted from the select list or named in the GROUP BY  clause. 

SQL:1999 and later permits such nonaggregates per optional feature T301 if they are functionally dependent on GROUP BY  columns: If such a relationship exists between name  and custid , the query is legal. This would be the case, for example, were custid  a primary key of customers . 

MySQL implements detection of functional dependence. If the ONLY_FULL_GROUP_BY  SQL mode is enabled (which it is by default), MySQL rejects queries for which the select list, HAVING  condition, or ORDER BY  list refer to nonaggregated columns that are neither named in the GROUP BY  clause nor are functionally dependent on them. 

MySQL also permits a nonaggregate column not named in a GROUP BY  clause when SQL ONLY_FULL_GROUP_BY  mode is enabled, provided that this column is limited to a single value, as shown in the following example: 

mysql> CREATE TABLE mytable ( -> id INT UNSIGNED NOT NULL PRIMARY KEY, -> a VARCHAR(10), -> b INT -> ); mysql> INSERT INTO mytable -> VALUES (1, ’abc’, 1000), -> (2, ’abc’, 2000), -> (3, ’def’, 4000); mysql> SET SESSION sql_mode = sys.list_add(@@session.sql_mode, ’ONLY_FULL_GROUP_BY’); mysql> SELECT a, SUM(b) FROM mytable WHERE a = ’abc’; +------+--------+ | a | SUM(b) | +------+--------+ | abc | 3000 | +------+--------+ 

It is also possible to have more than one nonaggregate column in the SELECT  list when employing ONLY_FULL_GROUP_BY . In this case, every such column must be limited to a single value in the WHERE clause, and all such limiting conditions must be joined by logical AND , as shown here: 

mysql> DROP TABLE IF EXISTS mytable; 

