Retrieving records fulfilling a condition using GROUP BY

mysql count group-by aggregate having
Moak picture Moak · Jan 4, 2010 · Viewed 8.9k times · Source

I'd like to only select the rows where the count is greater than 1 (in other words the duplicates) right now from a few thousand records I am mostly seeing 1s with a few 2s and 3s here and there 

SELECT count( * ) AS `Number` , GI . *
FROM `GeneralInformation` AS GI
GROUP BY `FirstName` , `Surname`

how can I do this?

Answer

Mike Tunnicliffe picture Mike Tunnicliffe · Jan 4, 2010 
SELECT count( * ) AS `Number` , GI . *
FROM `GeneralInformation` AS GI
GROUP BY `FirstName` , `Surname` 
HAVING count(*)>1

Related questions

How to get multiple counts with one SQL query?

I am wondering how to write this query. I know this actual syntax is bogus, but it will help you understand what I am wanting. I need it in this format, because it is part of a much bigger query. …

Read More
MySQL joins and COUNT(*) from another table

I have two tables: groups and group_members. The groups table contains all the information for each group, such as its ID, title, description, etc. In the group_members table, it lists all the members who are apart of each …

Read More
MySQL COUNT(*) GROUP BY HAVING COUNT=?

This my Query SELECT COUNT(*) as total, toys, date FROM T1 WHERE (date >= '2012-06-26'AND date < '2012-06-30') AND (Avail > '0') UNION SELECT COUNT(*) as total, toys, date FROM T2 WHERE (date &…

Read More