Counting duplicates of a field

[kritikal]

I've been given the task of analyzing the data from a user tracking app that somebody else wrote. The tables are huge, so I want to get this query right before I run it on the server.

I want to count the number of rows that exist where the cookie value occurs more than once. So kinda like finding duplicates, but then just a count of those duplicates.

The table looks like this:
cookie | timestamp | ip

So if the table had the following in it:
1 1 1
1 2 1
2 2 2
3 3 3
3 4 4
4 5 5
4 6 5

then I'd want the query to return 3 (since the cookie value in the third row only appears once). The cookie value '1', '3', and '4' show up more than once in the table. These are the ones I want to count, so I guess the rest of the columns can be ignored.

We're running MySQL 3.x, so no subqueries! =(

I've tried a few queries, but I wasn't able to get exactly what I needed. Using the GROUP BY clause and HAVING COUNT(cookie) > 1 was close, but I need the number of rows returned. Any help?

Thanks!

[travelbuff]

$query= mysql_query(SELECT cookie FROM table GROUP BY cookie HAVING count(*) >1);
$count = mysql_num_rows($query);

should give you a count of duplicate rows. However, if what you REALLY want to know is how many non-duplicate rows there are, use SELECT DISTINCT instead.

[xblue]

I may be totally wrong, but I first thought you wanted the count per cookie, not the total of duplicates. If so

SELECT cookie, COUNT(cookie)  FROM table GROUP BY cookie HAVING COUNT(cookie) >1

may do.

[kritikal]

I'm looking for the total number of duplicates (any occurence of more than once).

I think travelbuff's answer may do, but I was hoping to get the answer from the SQL query, not relying on num_rows.

[travelbuff]

You could try:

$count_duplicates = mysql_result(mysql_query("SELECT COUNT(ip) FROM table GROUP BY cookie HAVING COUNT(cookie) >1"),0); 

But I don't know if it will work 😕