DISTINCT and basic expressions

The idea

So far every column you've shown already existed in the table. Real questions need calculated values: profit per product, a price with tax, a full label made from two columns. SQL computes these on the fly, row by row, just like a formula you drag down a spreadsheet column.

And sometimes you don't want every row at all, just the different values: "which cities do our customers live in?" That's DISTINCT.

DISTINCT: unique values

The sales team wants to know which cities your customers live in, maybe to plan where to deliver next. If you just select the city column, you get one row per customer, so a city with several customers shows up several times:

SELECT city
FROM customers;

 city
------------
 Jakarta
 Bandung
 Surabaya
 Jakarta
 Yogyakarta
 Medan
 NULL
 Surabaya
 Denpasar
 Semarang
 Jakarta
 Makassar
(12 rows)

Jakarta appears three times. Add DISTINCT to keep each value once:

SELECT DISTINCT city
FROM customers
ORDER BY city;

 city
------------
 Bandung
 Denpasar
 Jakarta
 Makassar
 Medan
 Semarang
 Surabaya
 Yogyakarta
 NULL
(9 rows)

The NULL (Gita's unknown city) is also a value as far as DISTINCT is concerned, so it appears once, sorted last.

With several columns, DISTINCT keeps each unique combination:

SELECT DISTINCT status, channel
FROM orders
ORDER BY status, channel;

 status    | channel
-----------+---------
 cancelled | app
 cancelled | web
 completed | app
 completed | web
 pending   | web
 shipped   | web
(6 rows)

That's a quick way to see which combinations actually occur in your data, a very common first step when exploring a table.

Calculating columns

How much does the shop make on each product? That's the price minus what the product cost to buy in, and neither table has a column for it. You can write the calculation straight into the SELECT list, and the database works it out for every row:

SELECT name, price, cost, price - cost
FROM products
LIMIT 4;

 name                 | price  | cost   | ?column?
----------------------+--------+--------+----------
 French Press 600ml   | 185000 | 110000 |    75000
 Pour-over Kettle     | 320000 | 210000 |   110000
 Arabica Gayo 250g    |  95000 |  52000 |    43000
 Robusta Lampung 250g |  65000 |  35000 |    30000
(4 rows)

The new column is called ?column?, which is useless in a report. Give it a name with AS:

SELECT name, price, cost, price - cost AS margin
FROM products
ORDER BY margin DESC
LIMIT 4;

 name             | price  | cost   | margin
------------------+--------+--------+--------
 USB-C Hub        | 289000 | 170000 | 119000
 Pour-over Kettle | 320000 | 210000 | 110000
 Desk Lamp LED    | 245000 | 140000 | 105000
 Wireless Mouse   | 175000 |  98000 |  77000
(4 rows)

The name after AS is called an alias. Notice that ORDER BY can use it, because sorting happens after SELECT. (WHERE can't use it, since WHERE runs before SELECT. The aliases module covers this.)

The usual operators work: +, -, *, /, and % for remainder. Standard maths order applies, so use parentheses to be clear.

The integer-division trap

Here's a trap that catches almost everyone at least once. Ask PostgreSQL a simple question, what's 7 divided by 2:

SELECT 7 / 2 AS result;

 result
--------
      3
(1 row)

When both numbers are whole numbers (integers), PostgreSQL does integer division and drops the decimals. This trap ruins percentage calculations:

SELECT name, (price - cost) / price * 100 AS margin_pct
FROM products
LIMIT 3;

 name               | margin_pct
--------------------+------------
 French Press 600ml |          0
 Pour-over Kettle   |          0
 Arabica Gayo 250g  |          0
(3 rows)

Every margin is zero, because (185000 - 110000) / 185000 is 0 in integer maths. The fix: make one number a decimal. Writing 100.0 instead of 100 and multiplying first does it:

SELECT name, (price - cost) * 100.0 / price AS margin_pct
FROM products
LIMIT 3;

 name               | margin_pct
--------------------+---------------------
 French Press 600ml | 40.5405405405405405
 Pour-over Kettle   | 34.3750000000000000
 Arabica Gayo 250g  | 45.2631578947368421
(3 rows)

Now the maths is right, but nobody wants sixteen decimals. ROUND(value, digits) tidies it:

SELECT name, ROUND((price - cost) * 100.0 / price, 1) AS margin_pct
FROM products
ORDER BY margin_pct DESC, name
LIMIT 5;

 name               | margin_pct
--------------------+------------
 Gel Pen Set        |       60.5
 Ceramic Mug        |       60.0
 Notebook A5 Dotted |       60.0
 Scented Candle     |       60.0
 Bamboo Plant       |       58.3
(5 rows)

Joining text

Sometimes you want a single readable label instead of separate columns, like "French Press 600ml (Kitchen)" for a product list. || glues pieces of text together, one after another. Anything you want in between, like the space and the brackets here, goes in as text in single quotes:

SELECT name || ' (' || category || ')' AS label
FROM products
LIMIT 3;

 label
------------------------------
 French Press 600ml (Kitchen)
 Pour-over Kettle (Kitchen)
 Arabica Gayo 250g (Coffee)
(3 rows)

NULL spreads through calculations

Any calculation involving NULL gives NULL, because "unknown plus anything" is still unknown:

SELECT name, price * stock AS stock_value
FROM products
WHERE category = 'Home';

 name           | stock_value
----------------+-------------
 Scented Candle |     6750000
 Throw Pillow   |     4800000
 Bamboo Plant   |        NULL
(3 rows)

The Bamboo Plant's stock is unknown, so its stock value is unknown too. That's the honest answer. You'll learn to replace NULLs with a default in the NULL module.

Why this matters for clean code

Calculating in SQL keeps the logic next to the data: one query gives everyone the same margin, rather than five people each building their own formula in Excel. Name every calculated column with a clear alias (margin_pct, not x), and be deliberate about division. Clear names and correct types are what make a query a reliable source of truth.

Recap

• DISTINCT removes duplicate rows; with several columns, it keeps unique combinations.
• Calculations in SELECT run for every row; name them with AS alias.
• ORDER BY can use aliases; WHERE can't.
• Integer ÷ integer drops decimals: use 100.0 or ::numeric.
• ROUND(x, n) rounds to n decimals; || joins text.
• Any calculation with NULL gives NULL.