Aliases and CASE expressions

The idea

Two tools in this module make queries read like reports:

• Aliases give columns and tables short, clear names.
• CASE is SQL's if/then/else: it turns raw values into labels like "budget" or "premium", and lets you count several categories in a single query.

Column aliases

You've used AS to name calculated columns. A few more details:

SELECT name AS product,
       price AS "Unit Price (Rp)",
       price - cost AS margin
FROM products
ORDER BY margin DESC
LIMIT 3;

 product          | Unit Price (Rp) | margin
------------------+-----------------+--------
 USB-C Hub        |          289000 | 119000
 Pour-over Kettle |          320000 | 110000
 Desk Lamp LED    |          245000 | 105000
(3 rows)

• A simple alias (product, margin) is lowercase with underscores, like a Python variable.
• An alias with spaces or capitals needs double quotes: "Unit Price (Rp)". That's handy for a final report, but awkward to reuse, so prefer simple names in queries other code will read.
• AS is optional (price - cost margin works), but writing it makes the query easier to read.

Where can you use an alias? Only in clauses that run after SELECT:

(PostgreSQL also lets GROUP BY use an alias as a convenience, but many databases don't, so this track repeats the expression there.)

Table aliases

You can also give a table a short name and use it to say which table a column comes from:

SELECT p.name, p.price
FROM products AS p
WHERE p.category = 'Home';

 name           | price
----------------+--------
 Scented Candle |  75000
 Throw Pillow   | 120000
 Bamboo Plant   |  60000
(3 rows)

With one table it's optional. In the next module, when queries combine several tables that share column names, table aliases become essential.

CASE: if/then for each row

CASE checks conditions in order and returns the value of the first one that's true:

SELECT name,
       price,
       CASE
           WHEN price < 100000 THEN 'budget'
           WHEN price < 200000 THEN 'mid-range'
           ELSE 'premium'
       END AS price_band
FROM products
ORDER BY price;

 name                 | price  | price_band
----------------------+--------+------------
 Gel Pen Set          |  38000 | budget
 Notebook A5 Dotted   |  45000 | budget
 Ceramic Mug          |  55000 | budget
 Bamboo Plant         |  60000 | budget
 Robusta Lampung 250g |  65000 | budget
 Scented Candle       |  75000 | budget
 Desk Organizer       |  89000 | budget
 Arabica Gayo 250g    |  95000 | budget
 Toraja Kalosi 250g   | 110000 | mid-range
 Throw Pillow         | 120000 | mid-range
 Wireless Mouse       | 175000 | mid-range
 French Press 600ml   | 185000 | mid-range
 Desk Lamp LED        | 245000 | premium
 USB-C Hub            | 289000 | premium
 Pour-over Kettle     | 320000 | premium
(15 rows)

• Each WHEN condition THEN value is tested from the top. The first true one wins, and the rest are skipped.
• ELSE catches everything else. Without it, unmatched rows get NULL.
• The whole thing ends with END, then you name it with AS.

Because the first match wins, order matters. Watch what happens if the broader condition comes first:

SELECT name,
       price,
       CASE
           WHEN price >= 100000 THEN 'mid-range'
           WHEN price >= 200000 THEN 'premium'
           ELSE 'budget'
       END AS price_band
FROM products
WHERE price >= 200000;

 name             | price  | price_band
------------------+--------+------------
 Pour-over Kettle | 320000 | mid-range
 USB-C Hub        | 289000 | mid-range
 Desk Lamp LED    | 245000 | mid-range
(3 rows)

A Rp 320.000 kettle is labelled "mid-range", because price >= 100000 was true first, and the premium line is never reached. Order conditions from most specific to least specific (or use ranges that can't overlap).

Grouping by a CASE

A CASE creates a new column, and you can group by it like any other:

SELECT CASE
           WHEN price < 100000 THEN 'budget'
           WHEN price < 200000 THEN 'mid-range'
           ELSE 'premium'
       END AS price_band,
       COUNT(*) AS products
FROM products
GROUP BY 1
ORDER BY products DESC;

 price_band | products
------------+----------
 budget     |        8
 mid-range  |        4
 premium    |        3
(3 rows)

GROUP BY 1 means "group by the first column of the SELECT". It saves repeating a long CASE, and it's common in analytics work.

Conditional aggregation

This is one of the most useful patterns in analytics SQL. Count several things in one pass by putting a CASE inside an aggregate:

SELECT channel,
       COUNT(*) AS orders,
       SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed,
       SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM orders
GROUP BY channel
ORDER BY channel;

 channel | orders | completed | cancelled
---------+--------+-----------+-----------
 app     |     12 |        10 |         2
 web     |     28 |        16 |         8
(2 rows)

Each row adds 1 to completed if it's completed, 0 otherwise, so the SUM counts completed orders. You've turned status values into columns, just like a pivot table.

PostgreSQL has a neater spelling for the same thing, FILTER:

SELECT channel,
       COUNT(*) FILTER (WHERE status = 'completed') AS completed,
       ROUND(100.0 * COUNT(*) FILTER (WHERE status = 'cancelled') / COUNT(*), 1) AS cancel_rate
FROM orders
GROUP BY channel
ORDER BY channel;

 channel | completed | cancel_rate
---------+-----------+-------------
 app     |        10 |        16.7
 web     |        16 |        28.6
(2 rows)

The SUM(CASE ...) version works in every database. FILTER is shorter and reads better, but it's specific to PostgreSQL (and a few others).

Why this matters for clean code

CASE puts business rules in one visible place. "Premium means Rp 200.000 and up" lives in the query, not in someone's memory or a hidden Excel formula. When the rule changes, you change one line. Pair it with clear aliases and your query becomes the documentation of how a number is calculated.

Recap

• AS names columns and tables; quote aliases with spaces: "Unit Price".
• Aliases work in ORDER BY, not in WHERE or HAVING.
• Table aliases (products AS p, then p.name) prepare you for multi-table queries.
• CASE WHEN ... THEN ... ELSE ... END returns the value of the first true condition.
• SUM(CASE WHEN ... THEN 1 ELSE 0 END) or COUNT(*) FILTER (WHERE ...) counts subsets in one pass.