0

If I run below query in my phpmyadmin it throw me below error

> Error in query (1054): Unknown column 'QTY' in 'where clause'

SELECT User_Name,
       COUNT(User_Name) AS QTY
FROM preusage
WHERE QTY > 1
GROUP BY User_Name

Please somebody help me

Sadikhasan
  • 18,365
  • 21
  • 80
  • 122
Mizanur Rahman
  • 27
  • 4
  • you cannot use alias name in where conditions – Prasad Khode Oct 26 '14 at 12:30
  • possible duplicate of [Can you use an alias in the WHERE clause in mysql?](http://stackoverflow.com/questions/200200/can-you-use-an-alias-in-the-where-clause-in-mysql) – JJJ Oct 26 '14 at 12:38
  • This make no sense... Is your interest to count user_name usage ? Maybe you should prefer to check if exist (registering new user ?) If you really want to get user name used more than once, please use HAVING... But it could reveal a designing mistake... – Loenix Oct 26 '14 at 12:39

2 Answers2

3

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.

But in your case I guess instead you want to use having anyway (which can use previously defined aliases)

SELECT User_Name, COUNT(User_Name) as QTY 
FROM preusage 
GROUP BY User_Name
HAVING QTY > 1
juergen d
  • 201,996
  • 37
  • 293
  • 362
0

use having instead of where clause as you are using group by 

SELECT User_Name,
       COUNT(User_Name) AS QTY
FROM preusage
GROUP BY User_Name HAVING User_Name > 1
A.B
  • 20,110
  • 3
  • 37
  • 71