Aliases, Expressions & DISTINCT — Make Messy Output Look Like a Report

The situation this lesson prepares you for

Somebody asks for the five most valuable lines sitting in the warehouse. You do the thing the last lesson taught you: you ask by which measure, and they tell you — by what the stock is worth, price times units on the shelf.

So you write it. You multiply, you sort, you cut to five. The rows are right; you check them one by one.

And then you look at the result and you cannot send it to anybody, because the second column is headed ?column? and every number in it has four decimal places.

Nothing is broken. The query is finished and the output is not. This lesson is the difference between those two things — and it is a bigger difference than it looks, because the person who asked will judge the answer entirely by the second one.

By the end you will be able to name any column you like, build columns your table does not contain, turn a calculation back into money, and ask for one row per value instead of one row per record. You will also have met the one way that last trick quietly goes wrong.

Every query below runs on the Northwind database on PostgreSQL. Type each one into CloudBeaver and compare the result with the table under it. If that is not set up yet, the Learn Data guide gets it running.

The query that was right, and unusable

Here is the one from the story:

SELECT "ProductName",
       "UnitPrice" * "UnitsInStock"
FROM "Products"
ORDER BY "UnitPrice" * "UnitsInStock" DESC
LIMIT 5;

Two things to notice, and only one of them is a surprise.

The first: you asked for a column that does not exist in the table. There is no stock-value column in "Products". You built one out of two that are there, and the database was perfectly happy to hand it back. That is worth stopping on — the select list is not a list of columns, it is a list of things to work out for each row, and a column name is just the simplest possible thing to work out.

The second: the database had to call it something, and it had nothing to go on. PostgreSQL calls it ?column?. DuckDB echoes the arithmetic back at you as (UnitPrice * UnitsInStock). Neither is any use to a reader, and neither engine is being unhelpful — you invented the column, so naming it is your job.

AS: you name the column

Start with the easy half: the same query, with one column renamed. AS gives any column the heading you choose:

SELECT "ProductName" AS "Product",
       "UnitPrice" * "UnitsInStock"
FROM "Products"
ORDER BY "UnitPrice" * "UnitsInStock" DESC
LIMIT 5;

One heading fixed, one to go. Nothing about the data changed — same five products, same order, same numbers. Only what the first column is called on the way out.

And note what AS is not: it does not create a column, move a column, or change a value. It attaches a label to something on its way out of the query. That is the whole of it.

The quotes are worth a sentence of their own. A quoted alias can hold spaces and capital letters — AS "Stock value" on the next page, or AS "Who to ask for" on a customer list — because the double quotes are the same ones you have been putting around column names since lesson 2, doing the same job. Without them, AS Stock value is two words the parser cannot make sense of, and the query fails.

One thing to know about unquoted aliases

You will see AS StockValue written without quotes, and it works. But on PostgreSQL it does not come back as you typed it — an unquoted identifier is folded to lower case, so you get a heading called stockvalue. DuckDB keeps your capitals.

That is a difference you will trip over exactly once and then never forget. Quote every alias and the question never comes up.

Now name the one the database couldn't

Back to the query that started this. The fix is the one you just learned:

SELECT "ProductName" AS "Product",
       "UnitPrice" * "UnitsInStock" AS "Stock value"
FROM "Products"
ORDER BY "Stock value" DESC
LIMIT 5;

Now look at the last-but-one line. It says ORDER BY "Stock value" DESC — the sort is using the name you invented, instead of repeating the multiplication. That is not just tidier. The calculation now exists in exactly one place in the query, so when somebody decides stock value should be based on a different column, there is one line to change instead of two that must be kept in step.

And now read the data rather than the syntax. One product is carrying more than a third of the value on that list — 2813 against a five-row total of 8016.95 — which is the kind of thing a report exists to make visible. And the single most valuable line in the warehouse, Mishi Kobe Niku, 29 units at 97.00, is one the company has stopped selling: nobody is going to order it, and it is sitting at the top. You could not see either of those ten minutes ago, and it took one multiplication.

How far does the name reach?

ORDER BY could use the name. The obvious next question is whether WHERE can, and the obvious answer is yes — it is right there in the query. Try it:

SELECT "ProductName" AS "Product",
       "UnitPrice" * "UnitsInStock" AS "Stock value"
FROM "Products"
WHERE "Stock value" > 1000;

SQL Error [42703]: ERROR: column "Stock value" does not exist
  Position: 111

The database is not being awkward. At the moment WHERE runs, that column really does not exist yet.

The order your clauses take effect

You write a query in one order. The clauses take effect in another, and this is the running order that matters:

1. FROM — pick up the table.
2. WHERE — throw rows away. The select list has not been read, so your new name is not available yet.
3. SELECT — work out the columns. This is where your name gets created.
4. ORDER BY — runs last, so it can use the name.

This is the order the clauses take effect in, not a description of the work the database physically does. A query planner is free to reorder, combine and skip steps however it likes, as long as the answer comes out the same. What it may never do is let WHERE see a name that SELECT has not made yet — and that is the part you can rely on.

That is the whole explanation, and it is worth more than the rule it explains: you never have to memorise where an alias works, because you can derive it. It also hands you the next-but-one lesson almost free — when you meet HAVING and wonder why WHERE cannot filter a total, it is this same running order answering again.

The fix: say the calculation twice

SELECT "ProductName" AS "Product",
       "UnitPrice" * "UnitsInStock" AS "Stock value"
FROM "Products"
WHERE "UnitPrice" * "UnitsInStock" > 1000
ORDER BY "Stock value" DESC;

It looks like repetition because it is. The sort keeps the short name; only the filter pays.

ROUND: arithmetic makes ugly numbers

The Berlin office wants the same list in euros. Today's rate is 0.9137, so that is one multiplication.

SELECT "ProductName" AS "Product",
       "UnitPrice" * 0.9137 AS "Price in EUR"
FROM "Products"
ORDER BY "UnitPrice" DESC
LIMIT 5;

The prices went in with 2 decimal places, and not one of the 5 came back as money. The top row runs to 6 decimal places. 113.106923 is not a price — nobody can be charged it and no report can print it. The answer is not wrong; it is just carrying more precision than the question has.

That is worth a moment, because it is where the decimals come from. Multiplying two exact decimals adds their decimal places together. A price with two and a rate with four give you six, every time, whether or not you wanted them — and nothing in the query warned you.

SELECT "ProductName" AS "Product",
       ROUND("UnitPrice" * 0.9137, 2) AS "Price in EUR"
FROM "Products"
ORDER BY "UnitPrice" DESC
LIMIT 5;

ROUND takes two things: the number, and how many decimal places you want. Two, for money.

Notice where the brackets go. ROUND("UnitPrice" * 0.9137, 2) wraps the whole calculation, because what you want rounded is the answer. ROUND("UnitPrice", 2) * 0.9137 rounds the input instead and then multiplies — different arithmetic, and on this data it even looks plausible.

Two more things worth having:

• ROUND changes what comes out of the query. It changes nothing in the table. The stored price is untouched. You have rounded the copy you are handing over, which is the only place it was ever a problem.
• Both our engines round half away from zero, so 10.625 becomes 10.63, not 10.62. That is not something the SQL standard promises, so do not assume it of an engine you have not checked.

Concatenate text

An expression does not have to be arithmetic. Two pipe characters glue text end to end, and that is how two columns become one line somebody can read.

SELECT "ContactName" || ' at ' || "CompanyName" AS "Who to call"
FROM "Customers"
ORDER BY "CompanyName"
LIMIT 5;

The text in the middle is a string of its own, in single quotes, and the spaces inside those quotes are yours to get right. ' at ' has one at each end. Write 'at' instead and the database glues exactly what you gave it, with no error and no warning:

Maria AndersatAlfreds Futterkiste

That is the whole rule for ||: it joins precisely what you hand it and adds nothing.

DISTINCT: duplicate values

So far the problem has been bad headings and ugly numbers. Here is a different one: the same answer, over and over.

Sales ask which countries the company sells into. You know where that lives, so you ask for it:

SELECT "Country"
FROM "Customers"
ORDER BY "Country";

25 rows come back — one per customer, not one per country. Germany is in there 11 times. The question had 10 answers and you are about to hand over 25 lines.

Nothing is wrong with the query. You asked for every customer's country, and that is exactly what arrived.

SELECT DISTINCT "Country"
FROM "Customers"
ORDER BY "Country";

10. DISTINCT goes immediately after SELECT, and it throws away any row that is a copy of one already returned. 11 German customers collapse into a single row saying Germany.

DISTINCT counts the whole row

Your list goes round. It is useful. And somebody writes back: this is great — can we see which cities, too?

So you add the column. The word DISTINCT is still sitting right there at the front. Before you read on: how many rows come back?

SELECT DISTINCT "Country", "City"
FROM "Customers"
ORDER BY "Country", "City";

24. Adding City made the DISTINCT list more than twice as long — exactly as it should. Germany appears 11 times because each country-city pair is distinct. Nothing is wrong with the query or the result.

DISTINCT is not a function on the column next to it. It applies to the whole row you selected.

Two rows are copies only when every column matches. Germany-plus-Aachen and Germany-plus-Berlin are two different rows, so both survive — and the country column had no say in it at all. Every column you add gives the rows one more way to differ, and one more way to stay.

If you have been keeping count, one row is missing: 25 customers went in and 24 came out. That row is the rule working in the other direction. Two customers — Ana Trujillo Emparedados y helados and Antonio Moreno Taquería — are in the same city, so they hold the same value in both of the columns you selected:

Mexico and México D.F. are not two different places here — they are two different columns. One holds the country, the other holds the city (México D.F. is Mexico City). Both columns match across the two rows, so DISTINCT treats them as copies and keeps one. No other pair of customers in the table matches on both columns, which is why the count drops by exactly one and no further:

SELECT "Country", "City", COUNT(*)
FROM "Customers"
GROUP BY "Country", "City"
HAVING COUNT(*) > 1;

Run it and you get a single row back. That is your missing 25th.

That is also why DISTINCT is a poor tool for "getting rid of duplicates" in general. When a result has rows in it you did not expect, DISTINCT will often make them disappear — and it will have hidden the reason rather than fixed it. The usual cause is a join. When one row on one side matches several rows on the other, every match comes back, and the row count multiplies without anything looking wrong. Series 2 · Intermediate SQL comes back to exactly that. Until then: when extra rows surprise you, find out where they came from before you reach for DISTINCT.

When two columns is what you meant

None of that makes SELECT DISTINCT "Country", "City" a mistake. Put DISTINCT on exactly the columns that together define one row of your answer:

• one row per country → SELECT DISTINCT "Country" — 10 rows;
• one row per country-and-city pair → SELECT DISTINCT "Country", "City" — 24 rows.

Both are correct; they answer different questions. Here 24 is right for "which towns, in which countries, do we have customers in?" Add a column and one row means something new, so you are answering a new question — make sure it is the one you were asked.

Now build the thing you would actually send

Put the whole lesson into one query — and last lesson's, and the one before that:

SELECT "ProductName" AS "Product",
       "UnitPrice" AS "List price",
       ROUND("UnitPrice" * 0.9137, 2) AS "Price in EUR"
FROM "Products"
WHERE "Discontinued" = false
ORDER BY "UnitPrice" DESC
LIMIT 5;

Three named headings, a rounded calculation, a filter and a sort. Nothing to explain and nothing to apologise for.

Notice what is not wrapped in ROUND. "UnitPrice" is already money in the table, so rounding it would change nothing — you round what you work out, not what was already a price. Rounding every column on sight is a habit worth not forming.

Two details worth pointing at. The 2 most expensive products in the catalogue are not on this list — WHERE "Discontinued" = false removed them, because a price list for things nobody can buy is worse than no price list. And Tofu is where ROUND is doing the most visible work: the raw conversion is 21.243525, which is 6 decimal places of something nobody could be charged.

Two more things worth knowing

ROUND on a whole number of places. ROUND(x, 0) gives you a whole number and ROUND(x) usually does the same. If you need "always up" or "always down" rather than "to the nearest", those are CEIL and FLOOR, and they are worth recognising when you meet them.

Other spellings of ||. Some databases offer CONCAT(a, b, c), and PostgreSQL has format(). || is the standard operator, it is what you will see in other people's SQL, and it works on both of our engines — learn that one and recognise the rest.

What you can do now

For any query whose output somebody else is going to read:

1. Name every column you invented. If it is not a bare column name, it needs an AS — and quote the alias so it can have spaces and capitals.
2. Round anything that is money, at the point you produce it, and remember you are rounding the output rather than the data.
3. Join text with ||, and count the spaces inside the quotes.
4. Reach for DISTINCT when you want one row per value — and read your select list before you do, because DISTINCT sees all of it.
5. Make the heading and the query agree. Most of the damage in this lesson came from a report whose title said one thing while its select list quietly said another.

Next lesson: COUNT, SUM, AVG, MIN and MAX — because you can calculate along a row now, and you still cannot add a column up. That is the piece that finally answers "who is our biggest customer".

The koans: now you write the queries

Each koan is a tiny fill-in-the-blank exercise: a failing test with a ___ where the answer belongs. Fill it in, run it, watch red turn green — and the next one unlocks.

cd c:/DataPallas/db/datazeus
.\zeus.bat koans learnsql series1 _20

On macOS or Linux, run ./zeus.sh koans learnsql series1 _20 from the same folder. The three arguments are course, series and episode.

Says the lesson isn't there? Your copy of DataZeus predates this lesson — run .\zeus.bat update (or ./zeus.sh update) once to fetch the latest lessons, then run the command again. Koans you have already solved are kept.

The file you edit is:

c:\DataPallas\db\datazeus\tests\src\koans\groovy\datazeus\learnsql\series1\_20\DistinctAliasesExpressionsKoans.groovy

Open it in any editor — Notepad++, VS Code, whatever you have. Each koan has a ___ where the answer belongs. Replace one, save, re-run the command above, and watch that koan go green.

Ten koans, easiest first, in the order this lesson builds them. They deliberately use different tables from the lesson — you price up order lines and tidy the supplier list — because you learn an idea by applying it somewhere new, not by retyping a query you just read. They run on DuckDB: a single file, no server to start.

One idea here has no koan, on purpose. The alias that ORDER BY can see and WHERE cannot is the most useful rule in this lesson, and DuckDB — which the koans run on — happens to allow what PostgreSQL rejects. A koan would go green for the very answer CloudBeaver refuses, so that rule lives here and in the flashcards instead.