Why Can't I Use an Alias for an Aggregate in a Having Clause

Why can't I use an alias for an aggregate in a having clause?

In MS SQL, the only place (I'm aware of) that you can reference aliases is in the ORDER BY clause. The ability to reference aliases in other parts of the query is a feature that many other db platforms have and honestly it annoys me that Microsoft hasn't considered it a useful enough feature to add it.

Using alias in the WHERE and HAVING statements?

Only MySQL permits alises in HAVING, it is not standard SQL (see here: https://dba.stackexchange.com/questions/50391/why-does-mysql-allow-having-to-use-select-aliases ) please note that no other major RDBMS allows the use of aliases in WHERE or HAVING.

The reason you can't use aliases in WHERE (and HAVING) is because SELECT is actually evaluated after most other sub-clauses: https://stackoverflow.com/a/21693272/159145

A SELECT query is evaluated, conceptually, in the following order:

1. The FROM clause
2. The WHERE clause
3. The GROUP BY clause
4. The HAVING clause
5. The SELECT clause
6. The ORDER BY clause

So your query:

SELECT
    customer_id,
    address_id AS addressID 
FROM
    customer 
WHERE
    addressID = 5 

Is evaluated in this order:

1: FROM
    customer
2: WHERE
    address_id = 5
3: SELECT
    customer_id,
    address_id AS addressID

As you cans see, if the WHERE part referenced addressID instead of address_id the query execution engine would complain because addressID is not defined at that point.

MySQL does permit the referencing of (normal) aliases in HAVING by doing a (non-standard) neat trick where it partially evaluates the SELECT before it evaluates HAVING - and because MySQL has a handling of aliases that means the evaluation engine can be sure that the alias is valid (which is why most other RDBMS engines don't allow the use of aliases in HAVING when they otherwise should be able to). But you can't use an alias in WHERE because if there's a GROUP BY then it might render an alias meaningless, consider:

SELECT
    SUM( foo ) AS baz,
    created
FROM
    foo
WHERE
    baz > 5 -- Meaningless: the GROUP BY hasn't been evaluated yet, so `baz` is unavailable
GROUP BY
    created

MySQL explains this in their manual: https://dev.mysql.com/doc/refman/5.7/en/problems-with-alias.html

Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined.

The WHERE clause determines which rows should be included in the GROUP BY clause, but it refers to the alias of a column value that is not known until after the rows have been selected, and grouped by the GROUP BY.

Why can't I use alias in a count(*) column and reference it in a having clause?

See the document referenced by CodeByMoonlight in an answer to your recent question.

The HAVING clause is evaluated before the SELECT - so the server doesn't yet know about that alias.

1. First the product of all tables in the from clause is formed.
2. The where clause is then evaluated to eliminate rows that do not satisfy
   the search_condition.
3. Next, the rows are grouped using the columns in the group by clause.
4. Then, Groups that do not satisfy the search_condition in the having
   clause are eliminated.
5. Next, the expressions in the select clause target list are
   evaluated.
6. If the distinct keyword in present in the select clause, duplicate rows
   are now eliminated.
7. The union is taken after each sub-select is evaluated.
8. Finally, the resulting rows are sorted according to the columns
   specified in the order by clause.

Referring to a select aggregate column alias in the having clause in Postgres

Is there a setting in Postgres that will allow it to use column aliases in the having clause?

No. Implementations that allow references to SELECT-list entries in HAVING are going outside the standard.

You should use a subquery, e.g.

select
  c
from (
  select 
    sum(clicks) c
  from table
  where event_date >= '1999-01-01'
  group by keyword_id 
) x
where c > 10;

... or repeat the aggregate.

Column ALIAS not recognized in HAVING clause, unkown column error

What database is this? If it's something like MSSQL, you can't use aliases like that elsewhere in a query. You'll have to replicate the whole alias definition:

SELECT big+ugly+calculation AS foo
...
HAVING (big+ugly_calculation) = bar

Or wrap the query in another:

SELECT *
FROM (  SELECT *, big+ugly+calculation AS foo )
WHERE foo = bar

SQL aggregate function alias

The manual clarifies:

An output column's name can be used to refer to the column's value in
ORDER BY and GROUP BY clauses, but not in the WHERE or HAVING clauses;
there you must write out the expression instead.

Bold emphasis mine.

You can avoid typing long expressions repeatedly with a subquery or CTE:

SELECT state_name, no_big_city, big_city_population
FROM  (
   SELECT s.name AS state_name
        , COUNT(*)        FILTER (WHERE p.type = 'city' AND p.population >= 100000) AS no_big_city
        , SUM(population) FILTER (WHERE p.type = 'city' AND p.population >= 100000) AS big_city_population
   FROM   state s
   JOIN   place p ON s.code = p.state_code
   GROUP  BY s.name -- can be input column name as well, best schema-qualified to avoid ambiguity
   ) sub
WHERE  no_big_city >= 5
   OR  big_city_population >= 1000000
ORDER  BY state_name;

While being at it, I simplified with the aggregate FILTER clause (Postgres 9.4+):

• How can I simplify this game statistics query?

However, I suggest this simpler and faster query to begin with:

SELECT s.state_name, p.no_big_city, p.big_city_population
FROM   state s
JOIN  (
   SELECT state_code      AS code  -- alias just to simplify join
        , count(*)        AS no_big_city
        , sum(population) AS big_city_population
   FROM   place
   WHERE  type = 'city'
   AND    population >= 100000
   GROUP  BY 1  -- can be ordinal number referencing position in SELECT list
   HAVING count(*) >= 5 OR sum(population) >= 1000000  -- simple expressions now
   ) p USING (code)
ORDER  BY 1;    -- can also be ordinal number

I am demonstrating another option to reference expressions in GROUP BY and ORDER BY. Only use that if it doesn't impair readability and maintainability.

difference between where and having with respect to aliases

Basically because they where defined for different purposes. The WHERE clause is for records filtering and the HAVING clause is designed for filtering with aggregate functions (GROUP BY).
In your second query an implicit GROUP BY filtering is being used, so for instance, if you add another column to the SELECT clause you will end up with different results.

EDIT based on correction by Martin Smith

HAVING was created to allow filtering of rows resulting of a GROUP BY. When no GROUP BY is specified, the whole result is considered a group.

If neither a <where clause> nor a <group by clause> is specified,
then let T be the result of the preceding <from clause>

or

...the group is the
entire table if no <group by clause> is specified

EDIT 2
Now regarding the ALIAS:

The specification for the WHERE clause regarding the columns references in the search condition says this:

Each <column reference> directly contained in the <search
condition> shall
unambiguously reference a column of T or be an outer reference.

Refer to: 7.6 <where clause>, Syntax Rule 1.

The specification for the HAVING clause regarding the columns references in the search condition says this:

Each <column reference> directly contained in the <search
condition> shall unambiguously reference a grouping column of T
or be an outer reference.

Refer to: 7.8 <having clause>, Syntax Rule 1.

And a grouping column is defined as:

A column referenced in a <group by clause> is a grouping column.

So in conclusion the WHERE must reference a column of the table and the HAVING clause must reference a grouping column of the group of rows.

(Second Informal Review Draft) ISO/IEC 9075:1992, Database
Language SQL- July 30, 1992

Aggregate Function alias from select used in where clause

You can not use column aliases in your WHERE clause. Also, when comparing results of aggregations, you must use the HAVING clause, after the GROUP BY is applied.

Do something like this:

SELECT Users.UserID, 
    ClassCourses.CourseID,
    ClassCourses.MinMark,
    sum(StudentMark) as SMark    
FROM   Users 
INNER JOIN UserExams ON Users.UserID = UserExams.UserID
INNER JOIN Exams ON UserExams.ExamID = Exams.ExamID 
INNER JOIN ClassCourses ON Exams.ClassID = ClassCourses.ClassID AND Exams.CourseID = ClassCourses.CourseID
GROUP BY Users.UserID, ClassCourses.CourseID,ClassCourses.MinMark
HAVING ClassCourses.MinMark > sum(StudentMark)

MySQL: Use Aliased Fields in Aggregate Functions

No, you can't use an alias in the select-list or a WHERE clause. You can only use the alias in a GROUP BY, HAVING, or ORDER BY.

You can also use aliases defined in a subquery:

SELECT foo, SUM(foo) AS foo_sum
FROM (
  SELECT COUNT(*) AS foo
  FROM bar
);

---

Related Topics