- 01Getting Started
- 02SELECT Statement Basics
- 03Filtering Rows with WHERE
- 04Sorting Results with ORDER BY
- 05Combining Tables with JOIN
- 06Aggregating Data with GROUP BY
- 07Fuzzy Matching with LIKE
- 08Modifying Data with INSERT, UPDATE, and DELETE
- 09Introduction to Subqueries
- 10Creating a VIEW
- 11Ranking Rows with Window Functions
- 12Understanding Transactions
- 13Writing Comments in SQL
- 14Logical Operators: AND, OR, NOT
- 15Working with NULL Values
- 16INNER JOIN vs. LEFT JOIN
- 17Filtering Aggregates with HAVING
- 18Removing Duplicates with DISTINCT
- 19Combining Results with UNION
- 20Conditional Values with CASE
- 21Checking Existence with EXISTS
- 22Changing Table Structure with ALTER TABLE
- 23Self Joins
- 24Speeding Up Searches with INDEX
- 25GROUP BY with Multiple Columns
- 26Pagination with LIMIT and OFFSET
- 27Pattern Matching with GLOB
- 28Date Calculations with the date() Function
- 29Joining Three Tables
- 30Matching Multiple Values with IN
- 31Derived Tables: Subqueries in FROM
- 32The Basics of TRIGGER
- 33Readable Queries with WITH (CTEs)
- 34UPSERT: Insert or Update in One Statement
- 35Combining MIN, MAX, and AVG
- 36Restricting Input with CHECK Constraints
- 37Auto-Numbering with AUTOINCREMENT
- 38Inspecting Query Plans with EXPLAIN QUERY PLAN
- 39ORDER BY with Multiple Columns
- 40Conditional Aggregation with CASE
- 41Removing Objects with DROP TABLE and DROP VIEW
- 42Combining Multiple CTEs
- 43Date Range Search with BETWEEN
- 44Finding What's Missing with NOT EXISTS
- 45Substituting NULL with COALESCE
- 46String Functions: UPPER, LENGTH, and SUBSTR
- 47Converting Types with CAST
- 48Merging Grouped Values with GROUP_CONCAT
- 49Enforcing Integrity with FOREIGN KEY
- 50Capstone: Build a Mini Library Management System
Conditional Aggregation with CASE
This lesson covers conditional aggregation with CASE, so a single query can count several conditions at once. It's for anyone searching "SQL conditional COUNT CASE."
Nesting a CASE expression inside COUNT() lets you count only the rows that match a condition — computing "how many scored 80+" and "how many didn't" in the same query, side by side.
The example uses COUNT(CASE WHEN score >= 80 THEN 1 END), which counts 1 only for matching rows — non-matching rows become NULL (since ELSE is omitted) and are excluded from the count. This is a neat trick built on the fact that COUNT() ignores NULL values.
A common early mistake is not understanding why ELSE can be safely skipped — a CASE with no ELSE defaults to NULL for non-matches, and since COUNT() ignores NULL, the net effect is "count only the matching rows." Getting several conditional counts side by side in a single query — without running it multiple times and stitching results together — is a technique that meaningfully speeds up building dashboards and reports.
💡 The SQL engine may take a few seconds to load the first time you run code.