lesson1Title

lesson2Title

lesson3Title

lesson4Title

lesson5Title

lesson6Title

lesson7Title

lesson8Title

lesson9Title

lesson10Title

lesson11Title

sqlIntroAdvancedChapter1Title

sqlIntroAdvancedChapter2Title

lesson12Title

sqlIntroAdvancedChapter3Title

-- Count how many classes each student is enrolled in
SELECT students.name AS student_name,
  COUNT(enrollments.class_id) AS class_count
FROM students
LEFT JOIN enrollments
  ON students.student_id = enrollments.student_id
GROUP BY students.name;

# 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`.

```sql title="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

Using a LEFT JOIN allows you to include all rows from the left table, in this case, the students, even if there are no corresponding rows in the enrollments table. This is particularly useful for ensuring that students who are not enrolled in any classes still appear in the results with a count of zero, thus providing a complete overview.

### It is possible to use a LEFT JOIN to ensure that all students are included in a count of classes they are taking, even if they are not enrolled in any classes.

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`