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;
Suppress an inspection in the editor
Position the caret at the highlighted line and press Alt+Enter or click .
Click the arrow next to the inspection you want to suppress and select the necessary suppress action.