AP 15 - Use an alias for derived columns
Severity
WarningType
Readability
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