SQL and MySQL course Β· Module 6: Aggregation and grouping

Aggregate functions and COUNT

2 min read Β·
In this lesson3

An aggregate function is a spell that takes many rows and returns a single value from them - a summary. Instead of a list of scrolls you get one number.

In MySQL we have five basic aggregate functions: COUNT, SUM, AVG, MIN and MAX. We will start with the most commonly used one - COUNT, meaning "count".

COUNT(*) - count all rows

The asterisk means "all rows, regardless of their contents":

SELECT COUNT(*) FROM ksiazki;

This means: "How many scrolls lie on the ksiazki shelf?". The Library will return a single number - for example 150.

COUNT(column) - skips NULL

When you give a column name instead of the asterisk, MySQL counts only the rows in which that column is not empty (has no NULL value):

SELECT COUNT(data_zwrotu) FROM wypozyczenia;

This counts only the loans that have already been returned - because for books still on loan the data_zwrotu column is empty (NULL). This is an important difference: COUNT(*) counts all loans, while COUNT(data_zwrotu) counts only the finished ones.

COUNT(DISTINCT column) - count distinct values

The word DISTINCT tells MySQL to skip repetitions and count only the unique values:

SELECT COUNT(DISTINCT kraj) FROM autorzy;

This means: "From how many different countries do our authors come?". If five authors are from Greece, the country "Greece" is counted only once.

Remember these three variants - they are the foundation of the whole Hall of Numbers!

Spotted a mistake in this lesson?

Check yourself

Answer the questions from this lesson. Pick an answer to see right away whether it is correct.

  1. 1. What is an aggregate function in SQL?

  2. 2. How does COUNT(*) differ from COUNT(data_zwrotu)?

These are 2 of 3 questions for this lesson. Solve the rest in the game.

Hands-on tasks in the game

  • Code editor

    Write a query that counts the number of all rows in the ksiazki table.

  • Code editor

    Write a query that counts the number of unique values of the kraj column in the autorzy table.

  • Horizontal ordering

    Arrange the tokens on a single line to form a query that counts all books:

  1. QuestionWhat is an aggregate function
  2. QuestionCOUNT(*) versus COUNT(column)
  3. Coding taskCount all books
  4. QuestionCOUNT DISTINCT
  5. Coding taskNumber of different countries
  6. Put in orderA query counting books

Useful articles