Expressions, functions and CASE

SELECT does more than show columns — it calculates. This lesson covers arithmetic, working with text and “if … then” with CASE.

Arithmetic

+ - * / and % (remainder) all work:

SELECT name, price, price * 0.88 AS discounted FROM products LIMIT 5;

Watch out when dividing a whole number by a whole number. In PostgreSQL 7 / 2 = 3 (the fraction is dropped), while MySQL gives 3.5000. If you need a fraction, make one of the numbers a decimal: 7 * 1.0 / 2.

Rounding: ROUND

SELECT name, ROUND(price / 12, 0) AS per_month FROM products LIMIT 5;

ROUND(number, n) rounds to n decimal places. There are also CEIL (up) and FLOOR (down).

Text functions

Joining strings:

In PostgreSQL use the || operator or the CONCAT function:

SELECT first_name || ' ' || last_name AS full_name FROM students LIMIT 5;

Calculating with NULL: COALESCE

Any calculation involving NULL gives NULL: 5 + NULL = NULL. COALESCE(a, b) takes b when a is empty:

SELECT first_name, COALESCE(scholarship, 0) AS scholarship FROM students LIMIT 8;

CASE — “if … then”

SELECT student_id, score,
       CASE
         WHEN score >= 86 THEN 'excellent'
         WHEN score >= 71 THEN 'good'
         WHEN score >= 56 THEN 'pass'
         ELSE 'fail'
       END AS mark
FROM grades
LIMIT 8;

The conditions are checked top to bottom and the first match wins. If none matches, ELSE is used, and if there is no ELSE, the result is NULL.

Remember

• You can calculate inside SELECT; be careful when dividing whole numbers.
• COALESCE(x, 0) puts a value in place of NULL.
• CASE WHEN … THEN … ELSE … END picks a value by condition.