Is it possible to use an alias in a WHERE condition?
For example:
SELECT n.name, count(c.name) as freq FROM names as n, names as c
WHERE freq < 1 AND gender = 'f' order by rand() )
Or would I have to do this another way?
Asked
Active
Viewed 75 times
1
Sᴀᴍ Onᴇᴌᴀ
- 7,890
- 8
- 30
- 58
Patrick Pierson
- 107
- 6
-
1My spidey sense tells me you are looking for a `GROUP BY` somewhere. – PeeHaa Jun 11 '16 at 20:55
-
... I may be extremely tired. and that may be the exact thing i'm looking for. pardon me – Patrick Pierson Jun 11 '16 at 20:57
-
What are you trying to achieve? Can you give sample data and expected output? NB: the query is not valid. Do you have a valid one? – trincot Jun 11 '16 at 21:01
-
1) you query seems to be meanless 2) calculated field can not be used in where but can in having clause – splash58 Jun 11 '16 at 21:01
-
@trincot i'm trying to get the most common name in a database. IE "Emily", "John" or any other generic name. – Patrick Pierson Jun 11 '16 at 21:03
-
1`SELECT name, count(*) FROM names GROUP BY name ORDER BY 2 DESC LIMIT 1` – trincot Jun 11 '16 at 21:04
-
The question is worthy of a self-delete – Drew Jun 11 '16 at 21:30
-
You need `HAVING freq < 1` but this will never be true. – dnoeth Jun 11 '16 at 21:58
-
Possible duplicate of [Using column alias in WHERE clause of MySQL query produces an error](http://stackoverflow.com/questions/942571/using-column-alias-in-where-clause-of-mysql-query-produces-an-error) – Sᴀᴍ Onᴇᴌᴀ May 03 '17 at 21:16
-
Or [this answer](http://stackoverflow.com/a/200203/1575353) might be a solution (like dnoeth specified) – Sᴀᴍ Onᴇᴌᴀ May 03 '17 at 21:18