MySQL Aggregate Functions Without GROUP BY: Why and How?

MySQL Aggregate Functions without GROUP BY: Why and How?

Unlike other relational database management systems (RDBMSs) that raise an error when an aggregate function appears in the SELECT list without a GROUP BY clause, MySQL allows it. This behavior has intrigued developers, leading to the following query:

Why does MySQL permit aggregate functions without GROUP BY?

Answer:

MySQL's design philosophy allows for extended functionality beyond the SQL standard. Permitting aggregate functions without GROUP BY is one such extension.

When executing a query like SELECT col1,col2,sum(col3) FROM tbl1, MySQL effectively groups the rows by all of the non-aggregate columns (in this case, col1 and col2). It then returns the result of the aggregate function (sum of col3) for the single indeterminate group.

Earlier versions of MySQL allowed this behavior by default. However, starting from MySQL 5.7.5, the ONLY_FULL_GROUP_BY SQL mode was introduced to enforce stricter SQL compliance and disallow queries with aggregate functions without a GROUP BY. This mode can be enabled or disabled as needed. By default, it is enabled in MySQL versions 8.0 and higher.

Additional Notes:

• The documentation for group by handling in MySQL provides further details on this behavior.
• The ONLY_FULL_GROUP_BY SQL mode can help ensure compatibility with other RDBMSs and improve performance by avoiding unnecessary calculations.

The above is the detailed content of MySQL Aggregate Functions Without GROUP BY: Why and How?. For more information, please follow other related articles on the PHP Chinese website!