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;