Column ALIAS not recognized in HAVING clause, unkown column error

Question

SELECT u.*, zz.contid, zz.lng, zz.lat, zz.zip, zz.city, zz.state, (**calc distance**) AS distance FROM contacts zz INNER JOIN users u ON zz.id = u.id WHERE cond1 = 1, cond2=2, etc.. GROUP BY u.id HAVING MIN(distance) < 100

Error: unknown column distance

Any idea why this is? and how to fix it?

I appreciate any advice, many thanks in advance!

Since MIN() is an aggregate function, its argument must be a column value, not an alias. — Barmar
– Barmar, Commented Oct 29, 2013 at 15:23
I don't see what this has to do with PHP or sessions. Please correct the tags. — user1864610
– user1864610, Commented Oct 29, 2013 at 15:25
I added the sql tag. It would be helpful if you also added the specific DBMS you're using. — Barmar
– Barmar, Commented Oct 29, 2013 at 15:26

Marc B · Accepted Answer · 2013-10-29 15:23:15Z

3

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

answered Oct 29, 2013 at 15:23

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Barmar Over a year ago

The difference between HAVING versus WHERE is that HAVING can refer to aliases. However, it can't refer to an aggregate of an alias.

Andrew Over a year ago

But that's the point of HAVING, to check aggregates. Otherwise it would be in your where clause.

AnchovyLegend Over a year ago

I tried using distance in where clause and removing having, still getting unknown column error

Marc B Over a year ago

Did you read my first line? You can't use aliases like that elsewhere in the same query. changing it from 'having' to 'where' won't change the fact that you're trying to use an alias within the same query.

Barmar · Accepted Answer · 2013-10-29 15:29:06Z

You can't refer to an alias in an aggregate function. Move the query into a subquery, and then aggregate it in the outer query.

SELECT * FROM ( SELECT u.*, zz.contid, zz.lng, zz.lat, zz.zip, zz.city, zz.state, (**calc distance**) AS distance FROM contacts zz INNER JOIN users u ON zz.id = u.id WHERE cond1 = 1, cond2=2, etc..) subquery GROUP BY id HAVING MIN(distance) < 100

Collectives™ on Stack Overflow

Column ALIAS not recognized in HAVING clause, unkown column error

2 Answers 2

4 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Related