Aggregate functions and COUNT

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":

1SELECT 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):

1SELECT 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:

1SELECT 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!