select with count, group and where condition

Can someone clarify the syntax of a select statement I'm having trouble
writing? I have this query:

> select client_id, count(client_id) as c
from users group by client_id;
+-----------+---+
| client_id | c |
+-----------+---+
| 0 | 2 |
| 1 | 1 |
| 2 | 1 |
| 10 | 1 |
+-----------+---+
4 rows in set (0.01 sec)

which is the correct result set. However, what I actually need are the
rows where c > 1 (just the top row in the example above). What's the
syntax for that? My reading of the manual suggests this incorrect answer:

> select client_id, count(client_id) as c
from users where c > 1 group by client_id;
ERROR 1054 (42S22): Unknown column 'c' in 'where clause'

I've tried lots of other variations but can't work it out.

--
Derek Fountain on the web at http://www.derekfountain.org/

Derek Fountain wrote:
> Can someone clarify the syntax of a select statement I'm having trouble
> writing? I have this query:
>
>> select client_id, count(client_id) as c
> from users group by client_id;
> +-----------+---+
> | client_id | c |
> +-----------+---+
> | 0 | 2 |
> | 1 | 1 |
> | 2 | 1 |
> | 10 | 1 |
> +-----------+---+
> 4 rows in set (0.01 sec)
>
> which is the correct result set. However, what I actually need are the
> rows where c > 1 (just the top row in the example above). What's the
> syntax for that? My reading of the manual suggests this incorrect answer:
>
>> select client_id, count(client_id) as c
> from users where c > 1 group by client_id;
> ERROR 1054 (42S22): Unknown column 'c' in 'where clause'
>
> I've tried lots of other variations but can't work it out.
>

Since c is a calculated column, it's use in the WHERE clause is
meaningless. In your case, for instance, if you got the syntax correct,
you would get no output because each row examined would have a count of
1 - and therefore be discarded (setting the count back to 0).

Try

SELECT client_id, COUNT(client_id) AS c
FROM USERS
GROUP BY client_id
HAVING c > 1;

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

> Try
>
> SELECT client_id, COUNT(client_id) AS c
> FROM USERS
> GROUP BY client_id
> HAVING c > 1;

Works a treat, thanks. :)

--
Derek Fountain on the web at http://www.derekfountain.org/