PhpStorm 2023.3 Help

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

  1. Place the caret at the highlighted line and press Alt+Enter or click the Intention action icon.

  2. Click the arrow next to the inspection you want to suppress and select the necessary suppress action.

Last modified: 25 March 2024