Query not identifying value

Asked

Viewed 35 times

2

SQL command:

SELECT message, count(message) as amount
FROM `messages`
WHERE LENGTH(message) >= 4 AND amount < 30 AND message != '#ddd' AND message != 'ddd' 
GROUP BY message
ORDER BY amount DESC
LIMIT 3

#1054 - Unknown column 'amount' in 'Where clause'

why it does not identify the amount being that it was set early on?

mysql sql

  • @rray Hi? this code is not mine and I am a layman..

  • 1

    @user3163662 Disregard what I wrote, it counts yes, the response (updated) of the actual rray is correct (example).

1 answer

2


Unable to use a alias on the clase WHERE then it is necessary to redo the expression again or call the column by the original', WHERE does its check line by line so it is not possible to add a value in it what should be done is to group the result with GROUP BY and then check with the clause HAVING who does the same thing as WHERE but it works with aggregated results.

SELECT message, count(message) as amount
FROM `messages`
WHERE LENGTH(message) >= 4 AND message != '#ddd' AND message != 'ddd' 
GROUP BY message
HAVING count(message) < 30
ORDER BY amount DESC
  • 1

    @mgibsonbr when I made the first reply I imagined that only the count would solve after the other error remembered the having.

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.