GROUP BY

Totals per group with GROUP BY

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

Get one total per genre or author instead of one for everything.

GROUP BY makes a pile per value; each total is worked out per pile.

The database
10 rows
book_idtitleauthorgenreyearpages
1A Study in ScarletArthur Conan DoyleMystery1887108
2The Hound of the BaskervillesArthur Conan DoyleMystery1902256
3The Da Vinci CodeDan BrownThriller2003454
4Charlie and the Chocolate FactoryRoald DahlChildren1964155
5MatildaRoald DahlChildren1988240
6DraculaBram StokerHorror1897418
7EmmaJane AustenRomance1815474
8Pride and PrejudiceJane AustenRomance1813432
9FrankensteinMary ShelleyHorror1818280
10Harry Potter and the Philosopher's StoneJ.K. RowlingFantasy1997223
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 GROUP BY 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
Romance2
SELECT genre, COUNT(*) AS books
FROM books
GROUP BY genre;

One pile per genre, and COUNT(*) is worked out for each pile.

  • What does GROUP BY do?

    SELECT genre, COUNT(*) FROM books GROUP BY genre;

    It sorts the rows into groups with the same genre, then works out COUNT, SUM or AVG once per group. One row comes back per group.

  • What goes in the SELECT?

    The column you group by, so you can tell the groups apart, plus totals like COUNT(*). Any other column has no single value per group.

  • Can I group by two columns?

    Yes: GROUP BY genre, author gives one row per genre and author pair.

  • Where does GROUP BY go?

    After FROM and WHERE, before ORDER BY. WHERE picks rows first, then the rest are grouped.

  • How is this like Excel?

    It's a PivotTable with genre in Rows and Count in Values, or COUNTIF next to a UNIQUE list. In new Excel, GROUPBY does it in one formula.

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.

A column that is neither grouped nor totalled: SELECT genre, title, COUNT(*) ... GROUP BY genre
DatabaseHowNote
SQLite (these lessons)Allowedtitle comes from one row of each group, with no way to say which. Easy to get wrong without noticing.
PostgreSQLErrorEvery column must be in GROUP BY or inside a function like COUNT.
MySQL / MariaDBErrorWith ONLY_FULL_GROUP_BY, the default since MySQL 5.7. Older setups allow it, as SQLite does.
SQL ServerErrorEvery column must be in GROUP BY or inside a function like COUNT.
OracleErrorORA-00979: not a GROUP BY expression.
Sybase ASEAllowedA Transact-SQL extension: the groups are repeated for every row, which rarely helps.

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