CASE Expressions and Built-in Functions

CASE

CASE is SQL’s if-then-else. It checks conditions in order and returns the result for the first one that is true.

SELECT name,
       price,
       CASE
           WHEN price >= 500 THEN 'premium'
           WHEN price >= 50  THEN 'standard'
           ELSE 'budget'
       END AS price_band
FROM products
ORDER BY price DESC;

Without an ELSE, rows that match no condition get NULL.

Conditional aggregation

Putting CASE inside an aggregate counts or sums only some rows. This produces several summaries side by side in one pass:

SELECT COUNT(*) AS all_orders,
       SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) AS delivered,
       SUM(CASE WHEN status = 'shipped'   THEN 1 ELSE 0 END) AS shipped,
       SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM orders;

Handling NULL

COALESCE returns the first value in its list that is not NULL. Use it to supply a default.

SELECT name,
       COALESCE(city, 'Unknown') AS city
FROM customers;

NULLIF(a, b) returns NULL when the two values are equal. It is the standard way to avoid dividing by zero: x / NULLIF(y, 0).

Text functions

SELECT name,
       UPPER(name)             AS upper_name,
       LENGTH(name)            AS characters,
       SUBSTR(name, 1, 3)      AS first_three,
       REPLACE(name, ' ', '_') AS no_spaces,
       name || ' (' || category || ')' AS label
FROM products;

|| joins text in SQLite and PostgreSQL. MySQL and SQL Server use a CONCAT function. TRIM removes spaces from both ends of a value.

Number functions

SELECT name,
       price,
       ROUND(price * 1.18, 1)   AS with_tax,
       CAST(price AS INTEGER)   AS whole_price,
       ABS(price - 100)         AS distance_from_100
FROM products;

CAST converts a value to another type. Dividing two integers gives an integer in many databases, so 7 / 2 is 3. Multiply by 1.0 or cast one side to get a decimal result.

Date functions

Date functions differ more between databases than anything else in SQL. In SQLite, strftime formats a date and date shifts one.

SELECT order_id,
       order_date,
       strftime('%Y', order_date)    AS year,
       strftime('%Y-%m', order_date) AS month,
       date(order_date, '+7 days')   AS due_date
FROM orders
LIMIT 5;

Grouping by month is one of the most common report queries:

SELECT strftime('%Y-%m', order_date) AS month,
       COUNT(*) AS orders
FROM orders
GROUP BY month
ORDER BY month;

The number of days between two dates comes from julianday:

SELECT c.name,
       c.signup_date,
       MIN(o.order_date) AS first_order,
       CAST(julianday(MIN(o.order_date)) - julianday(c.signup_date) AS INTEGER) AS days_to_first_order
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
GROUP BY c.name, c.signup_date;

In PostgreSQL you would use DATE_TRUNC and EXTRACT, and in SQL Server DATEPART and DATEDIFF. The ideas are the same; look up the function names for the database you use.

Practice

Show each customer with a column segment that is “repeat” for customers with two or more orders, “new” for one order and “none” for no orders.