count(name) in where clause?

[OhLordy]

Is there any way of me getting this sort of query to work?

select name, count(name) as name_count from table where name_count>1 group by name;

Thanks
Rob

[OhLordy]

Sort of got it with this

SELECT id, name,count(name) as name_count
FROM table
WHERE name_count>1
GROUP BY name;

Only problem is I need all the id's. I'm basically trying to pull the id's for all records that have duplicate names.
Thanks
Rob

[drawmack]

SELECT id from table where 
name in (SELECT name, count(name) as name_count FROM table WHERE name_count>1 GROUP BY name);

[OhLordy]

That doesn't seem to work. I get an error at the start of the subquery. I didn't think MySQL could do subqueries anyway. I solved it using the same number of queries as I would have been able to just made the subsequent queries a little cleverer.
Cheers
Rob

[bad76]

You have to use HAVING...

SELECT name, count(name) as name_count
FROM table
WHERE date>10
HAVING name_count>1
GROUP BY name

[edit] i'm sorry, HAVING is after GROUP...

WHERE date>10
GROUP BY name
HAVING name_count>1
[/edit]