'invalid column name' while using the HAVING

Question

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.

score 8 · Accepted Answer · answered Jan 12 '16 at 10:36

8

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;

answered Jan 12 '16 at 10:36

Tushar Gupta

15,504
1
29
47

so that means that i cannot use the total_calories alias? where can i use it then? – Newton Jan 12 '16 at 10:52
1

the output of the query would have a field name 'total_calories' – HardLeeWorking Jan 12 '16 at 10:54
@Newton after the query has been executed – Tushar Gupta Jan 12 '16 at 10:56
so that field name is only used to display the results. i cannot use it anywhere in my query? – Newton Jan 12 '16 at 10:56
1

You can use the alias in an `order by` clause. For any other uses, you'll have to put this query in a sub-query, then the outer query can use the alias. – HoneyBadger Jan 12 '16 at 10:59
that doesnt mean you cant use it....if you see @jarlh answer ... its the way to use it – Tushar Gupta Jan 12 '16 at 10:59
ok. i was basically following khanacademy classes and this query appeared there. maybe they made a mistake or are using some older/other sql version – Newton Jan 12 '16 at 11:09

score 5 · Answer 2 · answered Jan 12 '16 at 10:52

5

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

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

answered Jan 12 '16 at 10:52

jarlh

42,561
8
45
63

score 3 · Answer 3 · answered Jan 12 '16 at 11:57

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

Excellent explanation. Thank you. – Winston Smith Feb 01 '22 at 18:09 — Winston Smith, Feb 01 '22 at 18:09

score 2 · Answer 4 · answered Jan 12 '16 at 10:35

2

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;

answered Jan 12 '16 at 10:35

HoneyBadger

14,750
3
34
48

'invalid column name' while using the HAVING

4 Answers4

Linked

Related