AP 15 - Use an alias for derived columns

Severity

Type

Problem

Any derived value, including aggregates, should be given a unique name unless the query is completely ad hoc. The query is now easier to read. The alias may be directly referred to in the filter if the database engine supports it.

SELECT col1
      ,SUM(col2*1.23)
  FROM sales
 GROUP BY col1
HAVING SUM(col2*1.23)>10500

Solution

We assign SUM(col2*1.23) the alias gross_price. We use the alias gross_price in the HAVING filter.

SELECT col1
      ,SUM(col2*1.23) gross_price
  FROM sales
 GROUP BY col1
HAVING gross_price>10500

Note: Using the alias in the HAVING clause is not supported by all databases.

Legitimate use of the anti pattern

None