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

SUM, AVG, MIN and MAX

2 min read Β·
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. 1. What will the query SELECT AVG(cena) FROM ksiazki; compute?

  2. 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:

  1. QuestionThe AVG function
  2. QuestionThe SUM function
  3. Coding taskAverage book price
  4. Coding taskThe most expensive scroll
  5. Coding taskThe cheapest book
  6. QuestionMIN and MAX
  7. QuestionFunction returning the largest value
  8. Coding taskTotal value of the treasury
  9. Click in orderA query with the AVG function

Useful articles