How to filter records with an aggregate function COUNT in SQL

Here's how you could format this into a cookbook using markdown:

How to Filter Records with an Aggregate Function COUNT in SQL

Problem

You have a table with multiple records and you need to filter the records based on the count of a specific column. How can you use SQL to count the occurrence of certain records and then filter based on the count?

Sample Data

Consider a simple Employee table:

MySQL Solution

SELECT Name, COUNT(Name) as Count
FROM Employee 
GROUP BY Name
HAVING COUNT(Name) > 1;

Explanation for MySQL Solution

In the MySQL solution, the COUNT(Name) function is used to count the occurrence of each Name in the Employee table. The GROUP BY clause is used to group the records based on the Name field. The HAVING clause is used to filter the grouped records where the count of Name is greater than 1.

PostgreSQL Solution

SELECT Name, COUNT(Name) as Count
FROM Employee 
GROUP BY Name
HAVING COUNT(Name) > 1;

Explanation for PostgreSQL Solution

In the PostgreSQL solution, the query is exactly the same as in MySQL. The COUNT(Name) function counts the occurrence of each Name in the Employee table. The GROUP BY clause groups the records based on the Name field. The HAVING clause is used to filter the grouped records where the count of Name is greater than 1. This shows that the usage of COUNT function and HAVING clause is similar in MySQL and PostgreSQL for this specific problem.