JOIN with Aggregates

In real-world SQL, it's common to join multiple tables and then summarize results using aggregate functions like COUNT(), SUM(), or AVG().

This allows you to answer questions like:

• How many classes each student is taking
• How much revenue each customer generated
• What is the average grade per course

Example: Count Classes per Student

As an example, let's say we have the following tables:

students

student_id	name
1	Alex
2	Sara
3	Daniel

enrollments

student_id	class_id
1	A1
1	B2
2	C3

Below is an example of a JOIN with an aggregate function.

Join with COUNT aggregate

SELECT 
  students.name, 
  COUNT(enrollments.class_id) AS class_count
FROM students
LEFT JOIN enrollments
  ON students.student_id = enrollments.student_id
GROUP BY students.name;

Output:

The query will return the number of classes each student is taking.

name	class_count
Alex	2
Sara	1
Daniel	0

LEFT JOIN ensures students with no enrollments are still included. COUNT(enrollments.class_id) counts how many classes each student is taking.

JOIN + Aggregation Patterns

Below is a table of common join and aggregation patterns.

Goal	Join Type	Aggregate Function
Count related entries	LEFT JOIN	COUNT()
Total related values	JOIN	SUM()
Average from related entries	JOIN	AVG()
Summarize multiple relationships	any	with GROUP BY

Combining JOINs with aggregation allows you to:

• Build complete and meaningful reports
• Track performance across relationships
• Generate business insights and metrics

Want to learn more?

Join CodeFriends Plus membership or enroll in a course to start your journey.

Learn moreSign In