CODEWITHSUNDEEP
sqlsql-servert-sqlhaving
Newton
Newton

Reputation: 428

'invalid column name' while using the HAVING

I am using Microsoft SQL Server 2014.

The following is my query

SELECT type, SUM(calories) AS total_calories
FROM exercise_logs
GROUP BY type
HAVING total_calories > 150;

and I get the error

Msg 207, Level 16, State 1, Line 2 Invalid column name 'total_calories'.

Can somebody point out what am I doing wrong? Thanks.

Upvotes: 4

Views: 7428

Answers (4)

jarlh
jarlh

Reputation: 44776

You can also wrap the GROUP BY query up in a derived table:

select type, total_calories 
FROM (
    SELECT type, SUM(calories) AS total_calories
    FROM exercise_logs
    GROUP BY type
) AS dt
WHERE total_calories > 150

Upvotes: 8

David Rushton
David Rushton

Reputation: 5030

The HAVING clause allows you to filter based on the the results of an aggregate function, like SUM, MIN and MAX. You must use these functions directly, unfortunately columns aliases from the SELECT clause cannot be reused here. This is a consequence of the Logical Processing Order. Taken from MSDN:

The following steps show the logical processing order, or binding order, for a SELECT statement. This order determines when the objects defined in one step are made available to the clauses in subsequent steps. For example, if the query processor can bind to (access) the tables or views defined in the FROM clause, these objects and their columns are made available to all subsequent steps. Conversely, because the SELECT clause is step 8, any column aliases or derived columns defined in that clause cannot be referenced by preceding clauses. However, they can be referenced by subsequent clauses such as the ORDER BY clause. Note that the actual physical execution of the statement is determined by the query processor and the order may vary from this list.

1.FROM

2.ON

3.JOIN

4.WHERE

5.GROUP BY

6.WITH CUBE or WITH ROLLUP

7.HAVING

8.SELECT

9.DISTINCT

10.ORDER BY

11.TOP

Upvotes: 3

Tushar Gupta
Tushar Gupta

Reputation: 15923

Aggregation is required , as you have no access to alias total_calories

SELECT   type,SUM(calories) AS total_calories 
FROM     exercise_logs 
GROUP BY type 
HAVING   SUM(calories) > 150;

Upvotes: 8

HoneyBadger
HoneyBadger

Reputation: 15150

You need the aggregate function in the HAVING:

SELECT   type
,        SUM(calories) AS total_calories 
FROM     exercise_logs 
GROUP BY type 
HAVING   SUM(calories) > 150;

Upvotes: 2

Related Questions