MySQL Aggregate Functions without GROUP BY clause

Question

In MySQL, I observed that a statement which uses an AGGREGATE FUNCTION in SELECT list gets executed though there is no GROUP BY clause. Other RDBMS products like SQL Server throw an error if we do so.

For example, SELECT col1,col2,sum(col3) FROM tbl1; gets executed without any error and returns the first row values of col1,col2 and sum of all values of col3. The result of the above query is a single row.

Can anyone please tell why does this happen with MySQL?

Thanks in advance!!

Answer 1

It's by design - it's one of many extensions to the standard that MySQL permits.

For a query like SELECT name, MAX(age) FROM t; the reference docs says that:

Without GROUP BY, there is a single group and it is indeterminate which name value to choose for the group

See the documentation on group by handling for more information.

The setting ONLY_FULL_GROUP_BY controls this behavior, see 5.1.7 Server SQL Modes enabling this would disallow a query with an aggregate function lacking a group by statement and it's enabled by default from MySQL version 5.7.5.

Answer 2

You have two points in your question:

1. Select with mixed with aggregated and not aggregated columns (which not presented in GROUP BY)
2. Select with aggregated columns without GROUP BY.

First one described well in @jpw answer.

The second one is possible by SQL standard. And result of this query consists of one row.

        a) If T is not a grouped table, then

          Case:

          i) If the <select list> contains a <set function specifica-
             tion> that contains a reference to a column of T or di-
             rectly contains a <set function specification> that does
             not contain an outer reference, then T is the argument or
             argument source of each such <set function specification>
             and the result of the <query specification> is a table con-
             sisting of 1 row. The i-th value of the row is the value
             specified by the i-th <value expression>.

set function means aggregate function.

P.S. result that query over empty table consists of one row with nulls (this is the difference between GROUP BY NULL query and query with out GROUP BY at all).