Lecture 45 – Filtering Groups with Statements

This lecture covers the HAVING clause and CASE statements.

We’ve learned how to filter individual rows using `WHERE` and how to group rows using `GROUP BY`. But what if you want to filter the *groups* themselves? For example, “Show me only the departments that have more than 50 employees.” You can’t use `WHERE` for this, because `WHERE` filters rows *before* grouping. For filtering groups, we use the `HAVING` clause.

The `HAVING` Clause

The `HAVING` clause is used to filter the results of a `GROUP BY` clause. It works very much like `WHERE`, but it operates on the aggregated results.

Let’s use our `Courses` and `Enrollments` tables. We want to find courses that have more than 1 student enrolled.

SELECT
    c.CourseName,
    COUNT(e.Student_ID) AS NumberOfStudentsEnrolled
FROM Courses AS c
INNER JOIN Enrollments AS e ON c.CourseID = e.Course_ID
GROUP BY c.CourseName
HAVING COUNT(e.Student_ID) > 1;

Here’s the order of operations:

1. `FROM` and `JOIN`s: Combine the `Courses` and `Enrollments` tables.
2. `GROUP BY`: Group the results by `CourseName`.
3. `COUNT()`: Calculate the number of students for each course.
4. `HAVING`: Filter these groups, keeping only those where the `COUNT()` is greater than 1.
5. `SELECT`: Display the `CourseName` and the `NumberOfStudentsEnrolled` for the remaining groups.

More on `CASE` Statements

We briefly saw `CASE` statements in the last lecture to convert grades to numbers. A `CASE` statement allows you to define different outputs based on different conditions. It’s like an `if-then-else` statement in programming.

The basic syntax is:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE resultN
END

Let’s use `CASE` to categorize students based on their major.

SELECT
    StudentName,
    Major,
    CASE
        WHEN Major = 'Computer Science' THEN 'Tech Major'
        WHEN Major = 'Mathematics' THEN 'STEM Major'
        WHEN Major = 'Physics' THEN 'STEM Major'
        ELSE 'Other Major'
    END AS MajorCategory
FROM Students;

This query will add a new column `MajorCategory` to our results, showing a custom category for each student’s major.

Combining `CASE` with Aggregates

You can combine `CASE` with aggregate functions. For example, let’s count how many students are ‘Tech Majors’ vs. ‘STEM Majors’ vs. ‘Other Majors’.

SELECT
    CASE
        WHEN Major = 'Computer Science' THEN 'Tech Major'
        WHEN Major = 'Mathematics' THEN 'STEM Major'
        WHEN Major = 'Physics' THEN 'STEM Major'
        ELSE 'Other Major'
    END AS MajorCategory,
    COUNT(StudentID) AS NumberOfStudents
FROM Students
GROUP BY MajorCategory;

In the Next Lecture…

We will explore more advanced SQL functions that help us manipulate strings, numbers, and dates. These functions are essential for cleaning and transforming data within your queries.

Lecture 44: Final Project – Advanced Queries