[RESOLVED] help with sql: count

[srisa]

Hello,
I have a table which apart from other columns, has id and rid columns. id is the primary key and rid is a reference to another table.
What I want to do is retrieve the rid's which have more than one row in the table.
I tried this

SELECT * FROM address where count(rid)>1;

which throws up an error.
I tried something with joins which returned all the rows.
What is the correct way to achieve what I want to?
Thank you.

[techgnome]

When using an aggregate in the filtering of the SQL, it can't be in the WHERE clause. It goes in the HAVING clause instead. The reason for this is because the WHERE will be executed first to filter out the data, then the aggregates are performed, THEN the HAVING clause it executed.

SELECT Rid FROM Address HAVING COUNT(Rid) > 1

-tg

[srisa]

Thank you. But that is returning only one value, while there are several rid's whose count is greater than 1.

[techgnome]

Try this:

SELECT RID, COUNT(RID) FROM Address GROUP BY RID

See how many RIDs really are duplicated.

-tg

[brucevde]

SELECT Rid, Count(*)
FROM Address
Group By Rid
HAVING COUNT(*) > 1

[srisa]

Thanks brucevde. Your query was spot on. Thanks again.