AP 26 - Avoid using WHERE to filter aggregate columns

Severity

Type

Problem

The WHERE clause in combination with a subquery can be used instead of the HAVING clause.

This makes the query hard to read and more complex.

SELECT col1
      ,sum_col2
  FROM( SELECT col1
              ,SUM(col2) sum_col2
          FROM table1
         GROUP BY col1)
 WHERE sum_col2>10

Solution

Use the HAVING clause when filtering on aggregates

SELECT col1
      ,SUM(col2)
  FROM table1
 GROUP BY col1
HAVING SUM(col2)>10

Legitimate use of the anti pattern

None