Skip navigation links
A.5.5.5 Rollback Failure for Nontransactional Tables »
Section Navigation      [Toggle]

A.5.5.4. Problems with Column Aliases

You can use an alias to refer to a column in GROUP BY, ORDER BY, or HAVING clauses. Aliases can also be used to give columns better names: 

SELECT SQRT(a*b) AS root FROM tbl_name GROUP BY root HAVING root > 0;
SELECT id, COUNT(*) AS cnt FROM tbl_name GROUP BY id HAVING cnt > 0;
SELECT id AS 'Customer identity' FROM tbl_name;

Standard SQL doesn't allow you to refer to a column alias in a WHERE clause. This restriction is imposed because when the WHERE code is executed, the column value may not yet be determined. For example, the following query is illegal: 

SELECT id, COUNT(*) AS cnt FROM tbl_name WHERE cnt > 0 GROUP BY id;

The WHERE statement is executed to determine which rows should be included in the GROUP BY part, whereas HAVING is used to decide which rows from the result set should be used.


User Comments

Posted by M M on October 27 2005 1:14pm[Delete] [Edit]

Yes, this article is very important as it was for us, when we were creating a top of members.

We have created a method to calculate a score for each member, but we didn't wanted to add this score info, into a separate field, so we sent a query to mysql like this:

SELECT *, ((clicks/200)+(cookies/100)) AS score FROM members WHERE score>0 ORDER BY score DESC LIMIT 100

Of course that line is wrong, and the error is:
"#1054 - Unknown column 'score' in 'where clause'"

Then we have changed it with:
SELECT *, ((clicks/200)+(cookies/100)) AS score FROM members HAVING
score>0 ORDER BY score DESC LIMIT 100

and it worked like a charm.

Posted by Dizzee on May 22 2006 4:02am[Delete] [Edit]

Using aliases for column names:

SELECT column_1 AS `Title One`, column_2 AS `Title Two`, column_3 AS `Another Title` FROM yourDatabase

don't forget the backquotes

Posted by Christopher Morrissey on August 8 2006 3:48pm[Delete] [Edit]

SELECT * , MAX( expDate ) AS mExp
FROM subscriptionOrders
INNER JOIN users ON subscriptionOrders.userName = users.userName
WHERE users.accountType != 'downld'
AND subscriptionOrders.subscriptionType != 'download'
AND (
subscriptionOrders.subscriptionType = 'standard'
OR subscriptionOrders.subscriptionType = 'premium'
OR subscriptionOrders.subscriptionType = 'deluxe'
)
GROUP BY subscriptionOrders.userName HAVING mExp = CURDATE( )
ORDER BY subscriptionOrders.expDate DESC

Just an example that I am using for a CRON to expire accounts, I was having trouble using the HAVING statement untill i put it after the GROUP BY clause, hope this helps some one.

Add your own comment.