MySQL handling of GROUP by--official documentation

in standard SQL, a query this includes a GROUP BY clause cannot refer to nonaggregated columns in the select list that AR E not named in the GROUP BY clause. For example, this query was illegal in standard SQL because the name column in the select list does not appear in the :

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, the name column must is omitted from the select list or named in the GROUP BY clause.

MySQL extends the use of so, the GROUP BY select list can refer to nonaggregated columns no named in the GROUP BY Claus E. This means, the preceding query is legal in MySQL.You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this was useful primarily when all values are nonaggregated column not named in theGROUP BYIs the same for each group. The unless they is the same, and the values chosen are indeterminate, the Choose any value from each group. Furthermore, the selection of values from each group cannot is influenced by adding anORDER BYClause. Sorting of the result set occurs after values has been chosen, andORDER BYdoes not affect which values within each group the server chooses.

A Similar MySQL extension applies to The having  clause. In standard SQL, a query that includes A group by clause cannot refer to nonaggregated Co Lumns in The having  clause that is not named in the  group by  clause. A MySQL extension permits references to such columns to simplify calculations. This extension assumes that the nongrouped columns would have the same group-wise values. Otherwise, the result is indeterminate.

To disable the MySQL GROUP BY extension, enable the ONLY_FULL_GROUP_BY SQL mode. This enables standard SQL Behavior:columns not named in the GROUP BY clause cannot being used in the select list or HAVING Claus e unless enclosed in an aggregate function.

ONLY_FULL_GROUP_BYAlso affects use of aliases in the HAVING clauses. For example, the following query returns name values this occur only once in table orders :

SELECT name, count (name) from the orders  GROUP by name has  COUNT (name) = 1;

MySQL extends this behavior to permit the use of a alias in the HAVING clause for the aggregated column:

SELECT name, COUNT (name) as C from the orders  GROUP by name has  C = 1;

Enabling ONLY_FULL_GROUP_BY disables this MySQL extension and an non-grouping field ‘c‘ is used in HAVING clause error occurs because the column in the c HAVING clause are not Enclosed in an aggregate function (instead, it's an aggregate function).

The select list extension also applies to order by . That's, you can refer to nonaggregated columns in The order by  clause appear in The group by  clause. (however, as mentioned Previously, order by  does not affect which values is chosen from nonaggregated columns; It only sorts them after they has been chosen.) This extension does isn't apply if The only_full_group_by  sql mode is enabled.

In the some cases, you can use and to MIN() MAX() obtain a specific column value even if it isn't unique. If sort The column contains integers no larger than 6 digits, the following query gives the value of the From the column Row Co Ntaining the smallest sort value:

SUBSTR (MIN (CONCAT (Lpad (sort,6, ' 0 '), column)), 7)

See section 3.6.4, "The Rows Holding the group-wise Maximum of a Certain Column".

If you is trying to follow standard SQL, you cannot use expressions in GROUP BY clauses. As a workaround, use an alias for the expression:

SELECT ID, Floor (value/100) as Val   tbl_name   GROUP by ID, Val;

MySQL permits Expressions GROUP BY in clauses, so the alias is unnecessary:

SELECT ID, floor (value/100)   tbl_name   GROUP by ID, floor (value/100);

Http://dev.mysql.com/doc/refman/5.0/en/group-by-handling.html

MySQL handling of GROUP by--official documentation