Inspectopedia Help

Aggregate-related problems

Reports invalid usages of SQL aggregate functions.

The following situations are considered:

  • Columns that are used in HAVING and ORDER BY clauses but are missed in GROUP BY clauses.

    CREATE TABLE foo(id INT PRIMARY KEY, a INT, b INT); SELECT a, MAX(b) FROM foo GROUP BY a HAVING b > 0; SELECT * FROM foo GROUP BY a ORDER BY b;

    This rule does not apply when grouping is made by the primary key.

    SELECT * FROM foo GROUP BY id ORDER BY b;

  • Aggregate functions in a wrong context. Usually, you can use aggregate functions in the following contexts: a list of expressions in SELECT; in HAVING and ORDER BY sections; and other dialect-specific cases. The following queries will display an error.

    SELECT a FROM foo WHERE MAX(b) > 0; SELECT a FROM foo GROUP BY MAX(a);
  • Nested calls of aggregate functions.

    SELECT MAX(SUM(a)) FROM foo GROUP BY a;

    This rule does not apply to analytic functions. The following query is valid and correct.

    SELECT MAX(SUM(a) OVER ()) FROM foo;

  • Usages of HAVING without aggregate functions. In this case, consider rewriting your code using the WHERE section.

    SELECT a, MAX(b) FROM foo GROUP BY a HAVING a > 0;

Inspection Details

Available in:

AppCode 2023.3, CLion 2023.3, DataGrip 2023.3, DataSpell 2023.3, GoLand 2023.3, IntelliJ IDEA 2023.3, JetBrains Rider 2023.1, PhpStorm 2023.3, PyCharm 2023.3, Qodana for .NET 2023.1, Qodana for JVM 2023.3, Qodana for PHP 2023.3, Qodana for Ruby 2023.3, RubyMine 2023.3

Plugin:

Database Tools and SQL, 233.SNAPSHOT

Last modified: 13 July 2023