MySQL·MySQL from first query to production

Aggregation

Summarise rows with COUNT, SUM, AVG and filter groups with HAVING.

Beginner 13 min read Lesson 4 of 300 Challenge included

What you will be able to do

Use COUNT(*) for row counts.
Use COUNT(column) for non-null counts.
Filter groups with HAVING.
Include every non-aggregated column in GROUP BY.

In plain English

Aggregate functions collapse rows into one value per group. GROUP BY defines the groups, WHERE filters rows before grouping and HAVING filters groups after. COUNT(*) counts rows; COUNT(column) skips NULLs.

aggregate.sqlSQL

SELECT course_slug,
       COUNT(*) AS lessons,
       ROUND(AVG(minutes), 1) AS avg_minutes,
       MAX(minutes) AS longest
FROM lessons
GROUP BY course_slug
HAVING COUNT(*) >= 5
ORDER BY lessons DESC;

Your turn

Average minutes per course

Easy

Select each course_slug and its average minutes, rounded to one decimal.

SQL · starter

SELECT course_slug FROM lessons

Check your understanding before moving on.

Knowledge checkQuestion 1

Which clause filters aggregated groups?

Select an answer to continue

Study tools

Make it yours.

Keep a private note for this lesson, then turn the key ideas into flashcards you can revisit.

Sign in to save notes and flashcards.Both are private to your account and stay with your progress.

Finished reading?

Lock in what you just learned.

Mark this lesson complete to save your place and move one step closer to the certificate.

Next: Indexes

Lesson 4 of 300 · 0% of course