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

Question

I was wondering why can't I use alias in a count(*) and reference it in the having clause. For instance:

select Store_id as StoreId, count(*) as _count
    from StoreProduct
    group by Store_id
        having _count > 0

Wouldn't work.. But it works if I remove _count and use count(*) instead.

score 110 · Accepted Answer · edited May 23 '17 at 12:34

110

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.

First the product of all tables in the from clause is formed.

The where clause is then evaluated to eliminate rows that do not satisfy the search_condition.

Next, the rows are grouped using the columns in the group by clause.

Then, Groups that do not satisfy the search_condition in the having clause are eliminated.

Next, the expressions in the select clause target list are evaluated.

If the distinct keyword in present in the select clause, duplicate rows are now eliminated.

The union is taken after each sub-select is evaluated.

Finally, the resulting rows are sorted according to the columns specified in the order by clause.

edited May 23 '17 at 12:34

Community

1
1

answered Jan 15 '10 at 00:51

martin clayton

76,436
32
213
198

9

I'd add that the order of evaluation is a logical concept in SQL. In actuality, the query optimizer can choose a different order of operation, as long as the results are the same as if the logical order had been followed. – Shannon Severance Jan 15 '10 at 00:55
1

Basically the alias defined in the select clause would have two uses: 1) Naming the column 2) Sorting – Andre Pena Jan 15 '10 at 01:03
3

http://blog.sqlauthority.com/2009/04/06/sql-server-logical-query-processing-phases-order-of-statement-execution/ not really the question at hand but he has a good list of the order sql executes. Sure there is a more official list somewhere. 1. FROM 2. ON 3. OUTER 4. WHERE 5. GROUP BY 6. CUBE | ROLLUP 7. HAVING 8. SELECT 9. DISTINCT 10 ORDER BY 11. TOP – JKG Jan 15 '10 at 01:27
I Have similiar example and it works in MySQL, SqLite, but not MSSQL. What is the reason? – Tomas Kubes Aug 06 '13 at 15:32
SELECT examination.ID,examination.VALID,examination.CHANGE_ID ,MAX(analysis.LAST_CHANGE) as lastAnalysisChanged FROM examination LEFT JOIN (analysis) ON (analysis.examination_id = examination.id) WHERE examination.STATION_ID = 1 AND examination.STARTED <= '2013-07-23 17:21:17' AND examination.CREATED <= '2013-07-23 17:21:17' GROUP BY examination.id HAVING lastAnalysisChanged <= '2013-07-23 17:21:17' OR lastAnalysisChanged IS NULL ORDER BY (CASE WHEN MAX(ANALYSIS.LAST_CHANGE) IS NULL THEN EXAMINATION.STARTED ELSE MAX(ANALYSIS.LAST_CHANGE) END) ASC – Tomas Kubes Aug 06 '13 at 15:36
@qub1n - please post that as a separate question. You might want to add details of how it doesn't work. – martin clayton Aug 06 '13 at 17:20
{ SELECT SUM(amount),id FROM table GROUP BY id HAVING SUM(amount) >0 ; } So if 'HAVING' executes before SELECT does that mean that an aggregate function would be calculated twice. – cjava Dec 27 '13 at 12:08
1

@cjava: "does that mean that an aggregate function would be calculated twice". For PostgreSQL the answer is no (for the most cases) in compliance with this question: [How to avoid invoking functions twice when using GROUP BY and HAVING?](http://dba.stackexchange.com/a/119578/50284) – bartolo-otrit Nov 13 '15 at 18:01

score 17 · Answer 2 · answered Jan 15 '10 at 00:52

The select clause is the last clause to be executed logically, except for order by. The having clause happens before select, so the aliases are not available yet.

If you really want to use an alias, not that I'd recommend doing this, an in-line view can be used to make the aliases available:

select StoreId, _count
from (select Store_id as StoreId, count(*) as _count
    from StoreProduct
    group by Store_id) T
where _count > 0

Or in SQL Server 2005 and above, a CTE:

; with T as (select Store_id as StoreId, count(*) as _count
    from StoreProduct
    group by Store_id)
select StoreId, _count
from T
where _count > 0

score 5 · Answer 3 · answered Jan 15 '10 at 00:50

5

You can use the alias for count in the select clause, you just can't use it in the having statement, so this would work

select Store_id as StoreId, count(*) as _count
    from StoreProduct
    group by Store_id
        having count(*) > 0

answered Jan 15 '10 at 00:50

Glenn Slaven

33,720
26
113
165

score 3 · Answer 4 · answered Jan 15 '10 at 00:55

The aliases for the field names is only for naming the columns in the result, they can never be used inside the query. You can't do like this either:

select Store_id as Asdf
from StoreProduct
where Asdf = 42

However, you can safely use count(*) in both places, and the database will recognise that it's the same value, so it won't be calculated twice.

score 1 · Answer 5 · edited May 17 '17 at 00:38

1

Here is my contribution (based on the code posted here):

select * from (
  SELECT Store_id as StoreId, Count(*) as StoreCount 
  FROM StoreProduct
  group by Store_id
  ) data
where data.StoreCount > 0

edited May 17 '17 at 00:38

answered May 17 '17 at 00:20

New Dawg Learning Old Tricks

11
1

score 0 · Answer 6 · answered Jan 15 '10 at 00:52

You can use the alias for the aggregates in SQL, but that is just to show the alias in the results headers. But when you want to have a condition with the aggregate function in the having you still need to use the aggregate because it evaluates the function and not the name.

score 0 · Answer 7 · answered Jul 28 '16 at 10:09

In Hive 0.11.0 and later, columns can be specified by position if hive.groupby.orderby.position.alias is set to true.

set hive.groupby.orderby.position.alias=true;
select Store_id as StoreId, count(*) as _count
from StoreProduct
group by 1

I'm don't understand the purpose of your query. Given the context of the query you posted, your condition is not necessary because items that do not exist, i. e. count 0, will never be a result from a query...

score -1 · Answer 8 · answered Jan 15 '10 at 00:51

-1

Probably because that's the way sql defines the namespaces. take, for example:

  select a as b, b as a
    from table
   where b = '5'
order by a

what do a and b refer to? The designers just chose to make the aliases only appear on the "outside" of the query.

answered Jan 15 '10 at 00:51

Jimmy

89,068
17
119
137

The aliases are available in the `order by` clause. In your example the result will be sorted by `table.b` that is aliased `a`. Try `select 1 as a order by a`. That will be valid. This is due to the logical order of evaluation, `order by` comes after select and can see the aliases. – Shannon Severance Jul 13 '16 at 06:22

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

8 Answers8

Linked