Summarizing and grouping · Lesson 5 of 20

Aggregates: COUNT, SUM, AVG

Turn a whole table into one number, and mind the NULLs.

8 minutes3 games

Sometimes the question isn't "give me rows" but "how many?" or "what's the average?". Aggregate functions take a whole column and return a single value:

COUNT counts, SUM adds, AVG averages, MIN and MAX find the extremes.

SQL

SELECT COUNT(*) AS students,
       SUM(xp) AS total_xp,
       ROUND(AVG(xp), 1) AS avg_xp,
       MIN(xp) AS lowest,
       MAX(xp) AS highest
FROM students;

xp =

320[0]

150[1]

90[2]

410[3]

NULL[4]

275[5]

60[6]

180[7]

AVG(xp) = 1485 ÷ 7, not ÷ 8: the NULL is left out of the calculation entirely.

What's the output?

SELECT COUNT(*), COUNT(xp), COUNT(DISTINCT city)
FROM students;

What will this code print?

Complete the code

Complete it to find the price of the most expensive course.

SELECT (price) AS top_price FROM courses;

Exercise

In courses: get the total hours as total_hours and the highest price as top_price.

Expected result:

total_hours | top_price
146 | 600

SQL

SELECT
FROM courses;

Learn more from

Played 0 of 3 games

SQL