MariaDB Tutorial / Choose Conditional Values and Aggregates with CASE

CASE is an SQL expression that chooses a result according to conditions. Use it for display states or conditional aggregation, and define branch order, ELSE behavior and NULL handling because each changes which result is produced.

Distinguish an expression from procedural control

This CASE produces one value inside a SELECT expression. It is distinct from a stored-program CASE statement controlling execution of SQL statements. Searched CASE chooses the first true condition, so overlapping ranges require deliberate ordering. A correct condition can still summarize the wrong unit. Compare COUNT/SUM semantics in Summarize Rows with COUNT, SUM, AVG, MIN, and MAX and the report reconciliation in Build and Reconcile Monthly Sales, Customer Totals, and Unsold Products.

Check the MariaDB documentation for the syntax and conditions used here: case operator.

Prepare products with different stock states

Connect to the learning server as course_user and select shopcourse. The command-line and UTF-8 SQL-file guide explains how to save the code as a UTF-8 file and execute it with SOURCE C:/mariadb-lab/lesson.sql;, replacing the path with the actual file. Run SQL inside the MariaDB client, rather than at the operating-system prompt.

Check SELECT VERSION(), DATABASE(); and SHOW TABLES; before creating products_24_lab. These names belong only to this exercise. If they exist, inspect their definitions and rows first; remove them only when you have established that they are disposable exercise objects. Do not replace existing application tables.

USE shopcourse;
CREATE TABLE products_24_lab (
  product_id INT NOT NULL AUTO_INCREMENT,
  product_name VARCHAR(100) NOT NULL,
  category VARCHAR(30) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  stock INT NOT NULL DEFAULT 0,
  note VARCHAR(100) NULL DEFAULT NULL,
  PRIMARY KEY (product_id),
  CONSTRAINT ck_p24_price CHECK (price >= 0),
  CONSTRAINT ck_p24_stock CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO products_24_lab (product_id,product_name,category,price,stock,note) VALUES (1, '노트', '문구', 3500.00, 20, NULL),
  (2, '볼펜', '문구', 1500.00, 0, ''),
  (3, '머그컵', '생활', 8000.00, 12, '선물 포장'),
  (4, '스탠드', '생활', 25000.00, 5, NULL),
  (5, '파일', '문구', 1500.00, 50, ''),
  (6, '케이블', '전자', 8000.00, 0, '호환 확인');

The SQL retains the original Korean sample strings to exercise UTF-8 consistently. Product identifiers 1–6 represent a notebook, pen, mug, lamp, folder and cable; their category strings mean stationery, household and electronics. Enter the string literals as shown, use the identifiers when comparing results, and save the file with UTF-8 encoding.

There are two zero-stock products, one with stock from one through nine and three with at least ten. These are practice categories, not a complete sales-availability policy; reservations, incoming supply and disabled products may require additional states.

Put narrower overlapping conditions first

SELECT product_id,product_name,stock,
 CASE WHEN stock=0 THEN '품절'
      WHEN stock<10 THEN '재고 적음'
      ELSE '판매 가능' END AS stock_label
FROM products_24_lab ORDER BY product_id;

IDs 2 and 6 receive the out-of-stock label, ID 4 the low-stock label, and 1, 3 and 5 the available label. These output labels remain Korean in the unchanged SQL. If stock < 10 is tested first, zero qualifies there and never reaches the out-of-stock branch. Reverse only the branch order to observe the difference, then restore it.

When ELSE is omitted and no condition qualifies, CASE returns NULL. Distinguish a missing input from an undefined output branch when investigating an apparently blank result.

Compare conditional SUM with conditional COUNT

SELECT SUM(CASE WHEN stock=0 THEN 1 ELSE 0 END) AS out_of_stock,
 COUNT(CASE WHEN stock=0 THEN 1 END) AS also_out_of_stock,
 COUNT(CASE WHEN stock=0 THEN 1 ELSE 0 END) AS all_non_null,
 SUM(CASE WHEN category='문구' THEN price*stock ELSE 0 END) AS stationery_value
FROM products_24_lab;

COUNT does not count only numeric ones. It counts non-NULL expression results, including zeros. Choose the SUM 1/0 or COUNT 1/NULL pattern intentionally, and remember the empty-input aggregate rules if no rows qualify at all.

Keep result types and missing-value policy explicit

Mixing arbitrary strings and numeric amounts across branches makes subsequent calculations and sorting harder to reason about. Return a numeric amount separately from a display label. Whether an unknown price should become zero is a business decision.

SELECT category,
 SUM(CASE WHEN stock>0 THEN 1 ELSE 0 END) AS available_products
FROM products_24_lab GROUP BY category ORDER BY category;

The in-stock counts are stationery two, household two and electronics zero. Filtering stock > 0 with WHERE first would remove the electronics group entirely. Conditional aggregation can retain that group with a zero, so choose the method according to the report’s required shape.

Inspect the row-level branches before trusting a total

Display the primary key, stock and CASE output before summing them. Check equality versus range boundaries, AND/OR parentheses and missing ELSE branches. This SELECT computes a result without changing the stored stock value.

Before repeating the exercise, inspect the selected database, actual table definitions and stored rows, and back up anything that must be kept. Drop only this article’s disposable practice objects, with child tables before their referenced parents. DROP TABLE removes both definitions and rows and cannot be undone by an ordinary ROLLBACK; keep cleanup separate from transaction experiments.

DROP TABLE shopcourse.products_24_lab;