using functions in where

alirezaok

please see this sql:

SELECT id, count( id ) AS tedad
FROM sms_sent
WHERE tedad > 2 GROUP BY 'message'
ORDER BY id DESC

it showes : Unknown column 'tedad' in 'where clause'
count( id ) AS tedad is in my sql

also this sql show error :

SELECT id, count( id ) AS tedad
FROM sms_sent
WHERE count( id ) > 2 GROUP BY 'message'
ORDER BY id DESC

what is soloution?

Lars_Berg

You need a having clause. Also your group by clause is incorrect since you have a string and not a column name

select id, count(id) as tedad
  from sms_sent
 group by <some column>
 having count(id) > 2
 order by id desc

bradgrafelman

In addition to what Lars Berg said, try using "GROUP BY COUNT(id)" -- I don't believe you can reference aliases in the WHERE clause.

johanafm

bradgrafelman;10942574 wrote:
I don't believe you can reference aliases in the WHERE clause.

You can't, but they can be used in the having clause.

Lars_Berg

You can't, but they can be used in the having clause.

That is not valid for most RDBMS.

johanafm

Iirc having was in the standard allready for SQL-92, so I'm a bit curious to know what rdbms does not support it.

What you should be able to do with HAVING according to the standard is reference columns that are part either of group by or an aggregate function.

I do know that MySQL has made things weird for having in the same way they allow non-grouped by columns to not be part of an aggregate function, with the same possible outcome: indeterminable result in some cases. But extending things (even in non-sensible ways) is not the same as not supporting them, as long as the standard is indeed covered. And as long as you make sure you stick to the standard, MySQL will behave correctly in this case.