Aggregate functions

Summing up a whole table into a single value: COUNT (star, column, DISTINCT), SUM, AVG, MIN, MAX, how NULL is treated and the ban on mixing an aggregate with a plain column.

19 cards
4 topics
Free

Study this deck

The outline, topic by topic

Each topic is weighted the way the exam weighs it.

Concepts
Questions
Sort and link
The numbers

A few cards from the deck

Aggregate function: what is it?
Show the answer
A function that reads several rows and returns a single value: a row count, a sum, an average, a minimum or a maximum.
COUNT, SUM, AVG, MIN and MAX are the five usual ones. Without GROUP BY, they sum up the whole table into a single row.
The integer note column of the eleve table holds 14, NULL, NULL and 6. What does SELECT SUM(note) / COUNT(*) FROM eleve return?
- 5
- 10
- NULL
- 20
Show the answer
5
SUM ignores the NULLs and gives 20, but COUNT(*) counts all 4 rows: 20 / 4 = 5. AVG(note), on the other hand, would divide by the 2 marks present and return 10.
… counts every row; … counts the different cities.
Show the answer
COUNT(*) counts every row; COUNT(DISTINCT ville) counts the different cities.
DISTINCT inside COUNT removes duplicates before counting, and also ignores NULLs.
COUNT(*): what is it?
Show the answer
The aggregate that counts every row, whether or not it contains NULLs.
On an empty table it returns 0, not NULL, unlike SUM or AVG.

Study this deck