JOIN with Aggregates

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

This approach lets you answer questions such as:

• 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

For example, consider 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 combined 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 returns 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	Use with GROUP BY

Why combine JOINs with aggregates?

Using JOINs together with aggregate functions allows you to:

• Create 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