Counting "repeats" in MySQL GROUP BY

Question

I'm wondering if there is a way, other than sub-queries and temp tables, to count how many repeat values are in the rows you've grouped.

For example, it's easy to get a UNIQUE value count, but I want to get a count of values that appear more than once.

Example:

SELECT url, HOUR(dttm), COUNT(DISTINCT visitorIP) UniqueVisitors,
COUNT("visitorIPs that visited more than once") RepeatVisitors
FROM table
GROUP BY url, HOUR(dttm)

Any ideas? All I can think of is sub-queries or another table.

add the structure in sqlfiddle to help you ASAP – jcho360 Jun 12 '12 at 19:59 — jcho360, Jun 12 '12 at 19:59

score 0 · Answer 1 · answered Jun 28 '12 at 06:11

0

This is possible with a left outer join to itself, but only if you have a primary key on the table to exclude exact rows in the count otherwise a temp table would probably do fine.

answered Jun 28 '12 at 06:11

DTecMeister

76
4

Counting "repeats" in MySQL GROUP BY

1 Answers1