HAVING

Filtering groups with HAVING

SQLite in your browser
Lesson concept What you'll do
1 / 4

Keep only the groups whose total passes a test.

HAVING filters the groups after GROUP BY, the way WHERE filters rows.

The database
10 rows
book_idtitleauthorgenreyearpages
1EmmaJane AustenRomance1815474
2Pride and PrejudiceJane AustenRomance1813432
3Charlie and the Chocolate FactoryRoald DahlChildren1964155
4A Study in ScarletArthur Conan DoyleMystery1887108
5MatildaRoald DahlChildren1988240
6DraculaBram StokerHorror1897418
7The Hunger GamesSuzanne CollinsScience Fiction2008374
8Catching FireSuzanne CollinsScience Fiction2009391
9FrankensteinMary ShelleyHorror1818280
10The Da Vinci CodeDan BrownThriller2003454
Click a name to type it
Your query
Hint: click the green edit button on the board, or type it yourself
Query output Ready to run

Run your query to see the result table.

Press the green Run button or Ctrl+Enter.

Study notes

How HAVING works

Rows into piles, piles into rows

books

titlegenre
DuneScience Fiction
DraculaHorror
EmmaRomance
1984Science Fiction
CarrieHorror
MockingjayScience Fiction
Pride and PrejudiceRomance
FrankensteinHorror

piles (GROUP BY genre)

result

genrebooks
Science Fiction3
Horror3
SELECT genre, COUNT(*) AS books
FROM books
GROUP BY genre
HAVING COUNT(*) >= 3;

HAVING tests each pile after counting: Romance has only 2, so it goes.

Written order vs the order SQL works in

YOU WRITE

  1. SELECT
  2. FROM
  3. WHERE
  4. GROUP BY
  5. HAVING
  6. ORDER BY
  7. LIMIT

SQL WORKS THROUGH IT

  1. 1. FROM take the table
  2. 2. WHERE keep some rows
  3. 3. GROUP BY make piles
  4. 4. HAVING keep some piles
  5. 5. SELECT pick and work out columns
  6. 6. ORDER BY sort
  7. 7. LIMIT keep the first few

WHERE runs before the piles exist, so it can't see COUNT(*). HAVING runs after.

  • What does HAVING do?

    GROUP BY genre HAVING COUNT(*) >= 3

    It keeps only the groups whose total passes a test. It's WHERE for groups.

  • What's the difference between WHERE and HAVING?

    WHERE picks rows before they're grouped, so it can't use COUNT or SUM. HAVING picks groups after they're counted. A query can have both.

  • What order do the parts go in?

    SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT.

  • Can HAVING use a different total?

    Yes: with HAVING SUM(pages) > 1000, the query can still show COUNT(*), but keeps only genres with over 1,000 pages.

  • How is this like Excel?

    Like filtering a PivotTable by its values ("Value Filters"), or FILTER on a COUNTIF column: =FILTER(A2:B9, B2:B9>=3).

Other databases

How other databases do it

The lessons run on SQLite. Most of SQL is the same everywhere; these are the places where this lesson's SQL differs.

Using the AS name in HAVING: HAVING books >= 3
DatabaseHowNote
SQLite (these lessons)WorksBut repeating COUNT(*) works everywhere.
PostgreSQLErrorRepeat the function: HAVING COUNT(*) >= 3.
MySQL / MariaDBWorks
SQL ServerErrorRepeat the function: HAVING COUNT(*) >= 3.
OracleWorks from 23aiOlder versions give ORA-00904: repeat the function, HAVING COUNT(*) >= 3.
Sybase ASEErrorRepeat the function: HAVING COUNT(*) >= 3.

Know someone fighting with a spreadsheet? Send them this page.