Extra topic · The maths kitchen

SQL CASE WHEN, ROUND and COALESCE for Beginners

Maths in a query, rounding, replacing NULLs with COALESCE and IFNULL, and CASE WHEN for if-then-else logic.

Covers: Maths · ROUND · COALESCE · IFNULL · CASE WHEN

The lesson

Every example runs against the same small pizza shop database: pizzas, customers, orders and staff.

# Arithmetic SQL can calculate while it reads: prices with tax, totals, and labels chosen by a rule, like 'cheap' or 'fancy'. This level covers the maths, empty values, and CASE for if-then-else. - `+` `-` `*` `/` add, subtract, multiply, divide - `ROUND(number, 2)` rounded to 2 decimal places In MySQL, / keeps the decimals: 5 / 2 is 2.5. > Round only when you show the number. Rounding in the middle of a calculation adds up small errors.

SELECT name,
  price * 2, ROUND(price * 1.2, 2)
FROM pizzas

# NULL in arithmetic Any calculation with a NULL in it gives NULL: 5 + NULL is NULL. > If a total comes out NULL, look for one empty value among the inputs.

SELECT id, tip, tip + 1 FROM orders

# Replace NULL: COALESCE - `COALESCE(tip, 0)` the tip, or 0 when it's NULL - `IFNULL(a, b)` the same, for exactly two values COALESCE can take more values: it returns the first one that isn't NULL. > IFNULL is MySQL's own name; COALESCE works in every database. Prefer COALESCE.

SELECT id, COALESCE(tip, 0)
FROM orders

# Choose a value: CASE - `CASE` start - `WHEN spicy = 1 THEN 'hot'` if this is true, use this value - `ELSE 'mild'` otherwise - `END` finish - `AS heat` names the column > Without ELSE, rows that match no WHEN get NULL. Always add an ELSE.

SELECT name,
  CASE WHEN spicy = 1 THEN 'hot'
    ELSE 'mild' END AS heat
FROM pizzas

Appian corner

In Appian, round() rounds, a!defaultValue(value, default) is your COALESCE, and if() or a!match() does the CASE work.

Practice questions

Try each one before opening the answer. To write and run them against the database in your browser, practise the level.

1. What does this return?
```
SELECT 10 + NULL
```
- 10
- 0
- NULL
- An error
Show the answer
NULL. Anything plus NULL is NULL. The ghost wins.
2. Each pizza with its price plus 20% tax, rounded to 2 decimals.
```
SELECT name, ___(price * 1.2, 2)
FROM pizzas
```
Show the answer
```
SELECT name, ROUND(price * 1.2, 2)
FROM pizzas
```
The crumb-tidying function.
3. Every order id with its tip, showing 0 when there was no tip.
Show the answer
```
SELECT id, COALESCE(tip, 0)
FROM orders
```
COALESCE(tip, 0)
4. Every pizza name with the price of 3 of them.
Show the answer
```
SELECT name, price * 3 FROM pizzas
```
price * 3
5. Every customer’s name and what to call them: their nickname, or their name when they have none, in a column called known_as.
Show the answer
```
SELECT name,
  IFNULL(nickname, name) AS known_as
FROM customers
```
IFNULL(nickname, name) AS known_as
6. Every pizza name, and a column called label: 'cheap' when the price is under 10, otherwise 'fancy'.
Show the answer
```
SELECT name,
  CASE WHEN price < 10 THEN 'cheap'
    ELSE 'fancy' END AS label
FROM pizzas
```
CASE WHEN price < 10 THEN 'cheap' ELSE 'fancy' END AS label
7. Boss question! Every order id with a tip_band column: 'none' when the tip is NULL, 'big' when it is 2 or more, otherwise 'small'.
Show the answer
```
SELECT id,
  CASE WHEN tip IS NULL THEN 'none'
    WHEN tip >= 2 THEN 'big' ELSE
    'small' END AS tip_band
FROM orders
```
CASE can have several WHENs. Check for NULL first: WHEN tip IS NULL THEN 'none' WHEN tip >= 2 THEN 'big' ELSE 'small' END

Practise Numbers & CASE