[leetcode/db] 182 duplicate emails

Solution 1: Using WHERE and a temporary table

SELECT Email FROM
(
SELECT Email, count(Email) AS cnt
FROM Person
GROUP BY Email
) AS Statisitcs
WHERE cnt > 1

Solution 2: Using HAVING

SELECT Email
FROM Person
GROUP BY EMAIL
HAVING count(Email) > 1

Let’s look at the doc for HAVING:

HAVING specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECTstatement. HAVING is typically used with a GROUP BY clause. When GROUP BY is not used, there is an implicit single, aggregated group.

The syntax for HAVING is as followed:

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

Duplicated emails existed more than one time. To count the times each email exists, we can use GROUP BY to count the apperance of every email.
While we could use a temporary table to do the selecting job, a more common way to add a condition to groups or aggregates is to use the HAVING clause, which is much simpler and more efficient. So Solution 2 is better.