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!!

It does not return the *first* row. It returns values from an *indeterminate* row. — Gordon Linoff, Feb 13 '15 at 11:38

score 7 · Accepted Answer · answered Feb 13 '15 at 10:27

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.

Query without `group by` but with aggregate functions is possible by sql standard and allows by `ONLY_FULL_GROUP_BY` mode. — sectus, Feb 16 '15 at 03:23

score 4 · Answer 2 · answered Feb 13 '15 at 11:35

A quote from the MySQL documentation, the page about the aggregate functions:

If you use a group function in a statement containing no GROUP BY clause, it is equivalent to grouping on all rows.

If you want a GROUP BY clause on your query then append GROUP BY NULL to it. I cannot tell about other RDBMS-es but on MySQL this is valid syntax. It works the same as the query without it.

Remarks about your query

A quote from your question:

"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 part with "the first row" is not something to rely on. It just happens most of the times that you get the first row.

Your query selects the columns col1 and col2 that are neither aggregate values nor functionally dependent on the columns in the GROUP BY clause. The query is not valid according to the SQL standard. MySQL allows it but its execution is undefined behaviour and the documentation about the handling of GROUP BY clearly states that:

... the server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate...

score 3 · Answer 3 · edited Jun 20 '20 at 09:12

You have two points in your question:

Select with mixed with aggregated and not aggregated columns (which not presented in GROUP BY)
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).

MySQL Aggregate Functions without GROUP BY clause

3 Answers3

Remarks about your query

Linked

Related