Code Inspection: 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;