Learn You Some SQL For Great Good - Unlearn What You Have Learned OR How Select Logically Works (the HAVING clause)

"O Cassius, Brutus gave the word too early,
Who, having some advantage on Octavius,
Took it too eagerly; his soldiers fell to spoil,
Whilst we by Antony are all enclosed."
-- Shakespeare, Julius Caesar
Act V, Scene III, Lines 5-8

HAVING and a group

Next on our trip down the SELECT we find ourselves at HAVING.

1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. WITH CUBE or WITH ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP

HAVING can best be thought of as a WHERE clause for the GROUP BY.  HAVING applies a filter on the groups created by the GROUP BY clause.

A common need is to find when you have more than one of something in your data set.

SELECT
   a.name AS playwright
  ,COUNT(*) AS number_of_plays
  FROM plays AS p
  INNER JOIN playwrights AS a
    ON p.playwrightId = a.id
  WHERE a.birthEra = 'AD'
  GROUP BY a.name
  HAVING COUNT(*) > 1
;

(on SQL Fiddle)

By using the HAVING clause we are able to filter on the groups that the GROUP BY gives us, in the above we filter out the playwrights that have less than 1 play in our data set.  This filtering takes place after the filtering we do with the INNER JOIN (which gets rid of Jacopone da Todi) and the WHERE clause filtering which gets rid of the ancient playwrights.

Given the data set below.

CREATE TABLE plays (
  id [int] IDENTITY(1,1)
 ,name [varchar](100)
 ,playwrightId [int]
);

CREATE TABLE playwrights (
  id [int] IDENTITY(1,1)
 ,name [varchar](100)
 ,birthYear [int]
 ,birthEra [char](2)
);

INSERT INTO playwrights
(name, birthYear, birthEra)
VALUES
  ('Oscar Wilde', 1854, 'AD')
 ,('Euripides', 480, 'BC')
 ,('Aeschylus', 525, 'BC')
 ,('Sophocles', 497, 'BC')
 ,('William Shakespeare', 1564, 'AD')
 ,('Jacopone da Todi', 1230, 'AD')
;

INSERT INTO plays
(name, playwrightId)
VALUES
  ('The Importance of Being Earnest', 1)
 ,('Medea', 2)
 ,('Women of Troy', 2)
 ,('Orestes', 2)
 ,('Phoenician Women', 2)
 ,('Bacchae', 2)
 ,('Seven against Thebes', 3)
 ,('Agamemnon', 3)
 ,('The Libation Bearers', 3)
 ,('The Eumenides', 3)
 ,('Prometheus Bound', 3)
 ,('Oedipus Tyrannus', 4)
 ,('Oedipus at Colonus', 4)
 ,('Antigone', 4)
 ,('Electra', 4)
 ,('The Tempest', 5)
 ,('A Midsummer Night''s Dream', 5)
 ,('The Merchant of Venice', 5)
 ,('As You Like It', 5)
 ,('Romeo and Juliet', 5)
 ,('Julius Caesar', 5)
 ,('Macbeth', 5)
 ,('Hamlet', 5)
 ,('Othello', 5)
 ,('The Second Shepherds'' Play', 0)
 ,('Edward III', 0)
;

(on SQL Fiddle)