Foundation project

Project: a course activity report

Lesson 12 of 20 · 20 min · Module 4: Relationships & real reports

Build the kind of report a learning platform needs: activity for every course, including courses with no enrollments yet.

Start this lesson →

Free. No installation: you write and run the code in your browser, and it is checked automatically.

By the end, you’ll be able to…

Choose a LEFT JOIN to preserve empty groups
Count matching records correctly
Combine joins, grouping, null handling, and sorting

The idea

A LEFT JOIN retains rows from the left table even without a matching enrollment. Count a non-null enrollment key and use COALESCE to replace an empty SUM with zero.

SELECT c.title, COUNT(e.id) AS learners
FROM courses AS c
LEFT JOIN enrollments AS e ON e.course_id = c.id
GROUP BY c.id, c.title;

01Keep the empty course

An inner join would remove the course with no enrollments. Starting from courses and using a left join keeps that course available for the report.

02Count the right column

COUNT(*) counts the retained left-join row even when no enrollment matches. COUNT(e.id) counts only non-null enrollment ids, giving zero for the empty course. SUM over those missing values is NULL, so COALESCE supplies zero.

Your project brief

Return course title, enrollment count, and the sum of completed lessons for every course. Use 0 when no lessons exist. Sort by the completed-lesson total descending, then by title. Include the HTML & CSS course even though it has no enrollments.