SQL and MySQL course Β· Module 6: Aggregation and grouping
SUM, AVG, MIN and MAX
In this lesson4
COUNT counts rows, but sometimes we want to compute something from the numeric values in a column. Four more functions serve this purpose.
SUM - the sum of values
SUM adds together all the values in a column:
SELECT SUM(cena) FROM ksiazki;This means: "How much are all the books worth together?". The Library will add up the prices of all the scrolls.
AVG - the arithmetic mean
AVG (from average) computes the typical value:
SELECT AVG(cena) FROM ksiazki;This means: "How much does one book cost on average?". MySQL adds all the prices and divides by the number of books.
MIN and MAX - the extreme values
MIN returns the smallest value, and MAX the largest:
SELECT MIN(cena) FROM ksiazki;
SELECT MAX(cena) FROM ksiazki;The first query finds the cheapest book (its price), the second - the most expensive. MIN and MAX also work on dates (earliest and latest) and on text (alphabetically first and last).
Combining functions in one query
You can ask for several summaries at once, listing them separated by commas:
SELECT COUNT(*), AVG(cena), MIN(cena), MAX(cena) FROM ksiazki;One query, and the Library answers: how many books we have, what the average price is, the cheapest and the most expensive. Four numbers at a single glance!
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. What will the query SELECT AVG(cena) FROM ksiazki; compute?
2. What will the query SELECT SUM(cena) FROM ksiazki; compute?
These are 2 of 4 questions for this lesson. Solve the rest in the game.
Hands-on tasks in the game
- Code editor
Write a query that computes the average value of the cena column in the ksiazki table.
- Code editor
Write a query that returns the highest value of the cena column from the ksiazki table.
- Code editor
Write a query that returns the lowest value of the cena column from the ksiazki table.
- Code editor
Write a query that computes the sum of the values in the cena column of the ksiazki table.
- Click in order
Click the tokens in order to build a query that computes the average book price: