CASE with Aggregations — Conditional Counting and Summarizing

In the beginner course, you learned how to use CASE to create simple conditions inside a query. Now we combine CASE with aggregate functions like COUNT and SUM to perform conditional calculations.

A CASE with aggregation allows you to count or sum only specific rows based on a condition.

This pattern is perfect for:

• Counting rows that meet a condition
• Creating categories inside a query
• Summarizing data based on rules
• Building simple reports

Basic Pattern

SELECT column,
       SUM(CASE 
           WHEN condition THEN 1 
           ELSE 0 
       END) AS new_column
FROM table_name
GROUP BY column;

The CASE statement assigns values, and the aggregate function summarizes them.

Example 1: Count students per program

SELECT program,
       COUNT(*) AS TotalStudents
FROM students
GROUP BY program;

This shows the total number of students in each program.

Example 2: Count students who started after 2020

SELECT program,
       SUM(CASE 
           WHEN start_year > 2020 THEN 1 
           ELSE 0 
       END) AS RecentStudents
FROM students
GROUP BY program;

This counts only students who started after 2020.

Example 3: Count high grades (A or A-) per course

SELECT course_id,
       SUM(CASE 
           WHEN grade IN ('A', 'A-') THEN 1 
           ELSE 0 
       END) AS HighGrades
FROM enrollments
GROUP BY course_id;

This counts strong performance per course.

Example 4: Count enrollments by grade type

SELECT course_id,
       COUNT(*) AS TotalEnrollments,
       SUM(CASE 
           WHEN grade = 'A' THEN 1 
           ELSE 0 
       END) AS GradeA_Count
FROM enrollments
GROUP BY course_id;

This compares total enrollments with a specific category.

Why Use CASE with Aggregations?

• It allows conditional counting inside a single query
• It replaces multiple queries with one clean query
• It helps create simple reports and summaries
• It is widely used in business reporting

CASE with Aggregations Summary

Feature	Description
Purpose	Conditional counting and summarizing
Works with	SUM, COUNT
Best for	Grouped analysis and reporting
Strength	Combines logic and aggregation

CASE with aggregation is like counting only certain types of items in a group. Instead of counting everything, you decide what should be included.

Practice Tasks (Your Turn!)

Task 1: Students Table

Count how many students started before 2021 in each program.

Task 2: Enrollments Table

Count how many students received grade B or lower in each course.

Task 3: Courses Table

Count how many courses belong to each department, and also count how many have 3 credits.