read the transcript!
person: every user has a different email right?
1 query later… person, now sad: oh no
This query uses
HAVING to find all emails that are shared by more than one user:
SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1
id 1, email email@example.com
id 2, email firstname.lastname@example.org
id 3, email email@example.com
HAVING is like
WHERE, but with 1 difference:
HAVING filters rows AFTER grouping and
WHERE filters rows BEFORE grouping.
Because of this, you can use aggregates (like
COUNT (*)) in a
HAVING clause but not with
HAVING example that finds months with more than $6.00 in income:
SELECT month FROM sales GROUP BY month HAVING SUM(price) > 6
month: Jan. item: catnip price: 5
month: Feb item: laser price: 8
month: March item: food price: 4
month: March item: food price: 3