PostgreSQL Foundations·A database you can operate

Joins and aggregates

Joins combine related rows; aggregates summarize groups.

Intermediate 15 min read Lesson 8 of 300 Challenge included

What you will be able to do

Use LEFT JOIN when the right side is optional.
Filter before grouping with WHERE; filter groups with HAVING.
Alias tables and qualify columns to keep complex queries readable.

In plain English

INNER JOIN keeps matches, LEFT JOIN preserves the left side. GROUP BY turns rows into groups; HAVING filters after aggregation. Read the join condition as a relationship, not as a syntax requirement.

report.sqlSQL

select
  c.slug,
  c.title,
  count(l.id) as lesson_count,
  coalesce(sum(l.minutes), 0) as total_minutes
from courses c
left join lessons l on l.course_slug = c.slug
where c.published = true
group by c.slug, c.title
having count(l.id) >= 3
order by total_minutes desc;

Your turn

Count lessons per course

Medium

Join courses to lessons and return each course slug with its lesson count, including courses with zero lessons.

SQL · starter

select c.slug, count(l.id)
from courses c
join lessons l on l.course_slug = c.slug
group by c.slug;

Check your understanding before moving on.

Knowledge checkQuestion 1

Which clause filters grouped results?

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 and query plans

Lesson 8 of 300 · 0% of course