Count and calculate

One answer from many rows

An aggregate function reads many rows and gives one value.

Function	Result
COUNT(*)	the number of rows
SUM(column)	the total of the values
AVG(column)	the average of the values
MIN(column)	the smallest value
MAX(column)	the largest value

SELECT COUNT(*) FROM books;
SELECT AVG(price) FROM books;

The result of an aggregate query has one row.

Names for columns

The column of COUNT(*) has a long name that is hard to read. Use AS to give the column a better name. This is called an alias.

SELECT COUNT(*) AS book_count FROM books;

Filter first, then calculate

WHERE runs before the aggregate function. Only the rows that match are counted or added.

SELECT SUM(price) AS fantasy_total FROM books WHERE genre = 'Fantasy';

Rounding

ROUND(value, digits) rounds a number. Averages often have many decimals, so rounding makes them easier to read.

SELECT ROUND(AVG(price), 1) AS average_price FROM books;

Your turn

Write one query for each task, one after the other. Use the column names that the tasks ask for.