COUNT

COUNT, SUM and AVG

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

Get one total for the whole table: a count, a sum or an average.

COUNT, SUM and AVG turn many rows into one.

The database
11 rows
book_idtitleauthorgenreyearpages
1EmmaJane AustenRomance1815474
2Charlotte's WebE.B. WhiteChildren1952184
3CarrieStephen KingHorror1974200
4The Great GatsbyF. Scott FitzgeraldFiction1925180
5MockingjaySuzanne CollinsScience Fiction2010390
6DraculaBram StokerHorror1897418
7The Time MachineH.G. WellsScience Fiction1895118
8Pride and PrejudiceJane AustenRomance1813432
9Angels & DemonsDan BrownThriller2000616
10The Fault in Our StarsJohn GreenRomance2012313
11The Fellowship of the RingJ.R.R. TolkienFantasy1954423
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 COUNT, SUM, AVG works

Rows into piles, piles into rows

books

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

one pile

result

books
8
SELECT COUNT(*) AS books
FROM books;

No GROUP BY: every row lands in one pile, so one row comes back.

  • What are aggregate functions?

    Functions that turn a whole column into one value: COUNT, SUM, AVG, MIN and MAX. The result has one row instead of one per book.

  • What's the difference between COUNT(*) and COUNT(series)?

    COUNT(*) counts rows. COUNT(series) counts the values in a column and skips NULLs. The two differ whenever the column has empty values.

  • Do SUM and AVG skip NULLs too?

    Yes. AVG(pages) divides by the number of books that have pages, not by every row. The table below shows a catch with AVG in some databases.

  • How do I round an average?

    ROUND(AVG(pages), 1) rounds to 1 decimal place, like Excel's ROUND.

  • How is this like Excel?

    COUNT(*) is ROWS on the table (every row), COUNT(column) is COUNTA on that column, SUM(pages) is =SUM(F2:F11), and AVG, MIN and MAX are AVERAGE, MIN and MAX.

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.

AVG of whole numbers (pages 223 and 250)
DatabaseHowNote
SQLite (these lessons)236.5AVG always gives a decimal.
PostgreSQL236.5000000000000000A decimal (numeric). ROUND(AVG(pages), 1) tidies it.
MySQL / MariaDB236.5000A decimal with 4 places.
SQL Server236Whole-number columns give a whole-number average, cut off, not rounded. Use AVG(pages * 1.0).
Oracle236.5A number with as many decimals as it needs.
Sybase ASE236Whole-number columns give a whole-number average, as in SQL Server. Use AVG(pages * 1.0).

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