SQL -- Possible to use COUNT() column in WHERE?

i would like to count a column and only display records where the count is greater than 1

i tried something like:

SELECT COUNT(foo) AS bar
FROM tbl
WHERE bar > 1

but that didn't work

any ideas?

I don't get what you are saying. count() returns a count of records. you can't use it to reference a record since it's not related to any one record.

****, i don't get what i was saying either URL

i should have said that i'm trying to group records by how many duplicates there are and i only want to see records that have more than one occurance

so i tried to use something like:

SELECT Name, COUNT(Name) AS foo
FROM tbl
WHERE foo > 1
GROUP BY Name

but that didn't work, i assume that you just can't use aggregates in WHEREs

any known workaround?

hope that clears it up, sorry for the confusion

You'd have to use the count(*) in the where and I'm not even sure if that would work:

select name from tbl where count(*)>1 group by name;

give it a try. If you have a unique identifier you could join the table to itself:

select t1.name,t1.id,t2id from tbl t1,tbl t2 where t1.name=t2.name and t1.id!=t2.id;

i get a "ERROR 1111: Invalid use of group function" whenever i try to use an aggregate function in a WHERE

but joining the table on itself worked great, thanks for the idea