Can you use an alias in the WHERE clause in mysql?

by

You could use a HAVING clause, which can see the aliases, e.g.

 HAVING avg_rating>5

but in a where clause you’ll need to repeat your expression, e.g. 

 WHERE (sum(reviews.rev_rating)/count(reviews.rev_id))>5

BUT! Not all expressions will be allowed – using an aggregating function like SUM will not work, in which case you’ll need to use a HAVING clause.

From the MySQL Manual:

It is not allowable to refer to a
column alias in a WHERE clause,
because the column value might not yet
be determined when the WHERE clause
is executed. See Section B.1.5.4,
“Problems with Column Aliases”.

More Related Contents:

  1. WHERE vs HAVING
  2. get first occurrence of last changed value of a column [closed]
  3. MySQL – Rows to Columns
  4. ROW_NUMBER() in MySQL
  5. multiple query same table but in different columns mysql
  6. How can I make SQL case sensitive string comparison on MySQL?
  7. quick selection of a random row from a large table in mysql
  8. Join tables with SUM issue in MYSQL
  9. Strange duplicate behavior from GROUP_CONCAT of two LEFT JOINs of GROUP_BYs
  10. Return default result for IN value regardless
  11. How can I search (case-insensitive) in a column using LIKE wildcard?
  12. Search for “whole word match” in MySQL
  13. sql joins as venn diagram
  14. How can I join multiple SQL tables using the IDs?
  15. MySQL, better to insert NULL or empty string?
  16. SQL: Repeat a result row multiple times, and number the rows
  17. SQL: selecting rows where column value changed from previous row
  18. Why do results from a SQL query not come back in the order I expect?
  19. How to generate data in MySQL?
  20. MySQL combine two columns and add into a new column
  21. Mysql: how to select groups having certain values?
  22. How to skip certain database tables with mysqldump?
  23. MySQL error 1241: Operand should contain 1 column(s)
  24. How to set a MySQL row to READ-ONLY?
  25. Mysql: Order by like?
  26. Getting first day of the week in MySql using Week No
  27. Using DISTINCT and COUNT together in a MySQL Query
  28. SQL date format convert? [dd.mm.yy to YYYY-MM-DD]
  29. Convert SQL Server query to MySQL [duplicate]
  30. comparison of truncate vs delete in mysql/sqlserver [duplicate]

Leave a Comment