In the previous lesson you returned data exactly as it's stored. But a database doesn't store the price with VAT, nor a product's margin, nor the amount of an order line: it stores the minimal pieces and everything else is computed at query time. In this lesson you'll learn to build those calculated columns with arithmetic and text expressions, to give them a readable name with AS, and to understand —using the logical execution order from 02-01— why that name can be used in some parts of the query and not in others. This is also where the most important expression of the whole course shows up: quantity * unit_price * (1 - discount).

1. Column aliases with AS
2. When an alias needs double quotes
3. Table aliases
4. Calculated columns: arithmetic over columns
5. The star expression: the amount of an order line
6. Text concatenation: || and CONCAT
7. ROUND for presenting money
8. Why an alias doesn't work in WHERE but does in ORDER BY
9. Common Mistakes and Tips
10. Exercises
11. Conclusion

An alias is the name you want a column to have in the result. You declare it with AS:

First of all, let's see why it's needed. Without an alias, an expression has no name:

?column? is PostgreSQL's way of saying "this has no name". A report with that header is useless, and a program accessing the result by column name will find nothing. With an alias:

(First 10 of 20 rows.)

The word AS is optional for columns. These two lines are equivalent:

Even so, always write it. Without AS, forgetting a comma turns one column into the alias of the previous one and the query runs with no error, giving a wrong result (you saw it in 01-03 and in 02-01):

An alias is an identifier, so it follows the rules from 01-03: unquoted it folds to lowercase and only accepts letters, digits and underscores.

This is the only legitimate use of double quotes in this course: presentation aliases meant for a report or an export. For any alias you're going to reuse inside the query, use lowercase snake_case and save yourself the quotes.

Dialect note: MySQL and SQLite also let you use backticks (`Price with VAT`) or single quotes as an alias delimiter. PostgreSQL doesn't: double quotes or nothing. Always write the standard form.

Tables take aliases too, and here AS is equally optional:

With a single table it looks like a whim: p.name adds nothing over name. Its value shows up in module 3, when a query combines several tables that have columns with the same name:

Without the aliases p and c, name would be ambiguous and PostgreSQL would answer column reference "name" is ambiguous. That's why in professional SQL almost every query carries table aliases.

Useful conventions for table aliases:

One difference that catches people out: if you give the table an alias, the original name stops being available. This fails:

A calculated column is an expression in the SELECT list. It's evaluated row by row, with that row's values.

(First 10 of 20 rows.)

In subtraction the result's scale is the larger of the two, so the margin comes out with two clean decimals. In multiplication it won't.

(First 6 of 20 rows.)

Three things to learn from this:

1. The parentheses are mandatory. Without them, price - cost / price * 100 would evaluate as price - ((cost / price) * 100), because * and / have higher priority than - (precedence table from 01-03). The result for product 1 would be 12.50 - 62.4 = -49.90. It raises no error: it gives an absurd number.
2. The division is decimal, not integer, because price is NUMERIC and not INTEGER. If the columns were integers, (price - cost) / price would give 0 for every row and then 0 * 100 = 0. It's the classic failure when computing percentages; you solve it by casting one operand to NUMERIC with CAST (module 6).
3. The tail of decimals is ugly. PostgreSQL computes the NUMERIC division with generous precision. To present it you need ROUND (section 7).

(A selection of rows out of the 20.)

Here stock is INTEGER and price is NUMERIC(10,2): when types mix, PostgreSQL promotes the integer to NUMERIC and the result keeps two decimals.

Careful with division by zero. If instead of stock * price you computed something / stock, product 13 (stock 0) would blow up the whole query with ERROR: division by zero. You protect against it with NULLIF, covered in module 6.

In 01-06 it was settled that orders has no total column and that a line's amount is:

This expression is going to show up in practically every remaining module. Let's write it for the first time:

(First 12 of 47 rows.)

Let's take the expression apart, because every piece matters:

Check line 6 by hand: 6 units × €1.95 = €11.70; with a 10 % discount, 11.70 × 0.90 = €10.53. It matches.

And notice the four decimals: 10.5300, not 10.53. The quantity is an integer and contributes no scale, unit_price contributes 2 and (1 - discount) another 2, so the result comes out with 4. Arithmetically it's correct and for adding up it's what you want, but to show it to somebody you have to round.

Six of the 47 lines carry a discount; these are their exact amounts:

There's a revealing detail there: line 24 is worth €34.0200 and the return for order 10 recorded in returns is €34.02. The data is consistent across tables, and checking that with a query will be one of module 3's exercises.

Frequent error: writing quantity * unit_price * discount. That computes the discounted amount, not the amount charged. For line 6 it would give €1.17 instead of €10.53.

The standard concatenation operator in SQL is ||:

You can concatenate text with numbers: PostgreSQL converts the number to text automatically.

(First 3 of 20 rows.)

Concatenating anything with NULL gives NULL. All the text is lost. This is one of SQL's most expensive surprises, and GreenStore has the data to demonstrate it: seven customers have referred_by_id set to NULL.

Seven rows with an empty source, not seven rows with the text and a gap. The literal 'Referred by customer ' vanishes completely, because the rule for nulls is that any operation with an unknown value produces an unknown value.

The CONCAT function ignores NULLs and replaces them with an empty string:

(A selection of rows out of the 15.)

The text isn't lost any more, but the result isn't right either: it says "Referred by customer" without saying which one. The real solution is to replace the null with sensible text using COALESCE, studied in lesson 06-04:

A comparison to keep handy:

| Aspect | a || b | CONCAT(a, b) | |---|---|---| | SQL standard | Yes | No (but very widespread) | | With NULL | Returns NULL | Treats the NULL as '' | | In MySQL | By default it doesn't concatenate: || is logical OR | It's the usual form | | In SQL Server | Doesn't exist; + is used | Available since 2012 | | In SQLite | Yes | Doesn't exist |

Important dialect note: in MySQL, 'a' || 'b' returns 0, because there || means OR. It only behaves as concatenation if the server has PIPES_AS_CONCAT mode enabled. If you write SQL that has to work on both MySQL and PostgreSQL, use CONCAT.

ROUND(expression, decimals) rounds to the number of decimals you specify:

Now that's a presentable table. And with it you can already answer a business question: the product with the highest percentage margin is the bamboo toothbrush (65.71 %), and the one with the lowest, the extra virgin olive oil (37.60 %) —the catalogue's flagship product is, precisely, the one that leaves the thinnest relative margin.

The same for the line amount:

(The 6 lines with a discount, out of the 47.)

Look at line 18: the exact value is 35.6250 and ROUND leaves it at 35.63. On NUMERIC types, PostgreSQL rounds "half away from zero", which is what any accountant expects.

Course rule: compute with the full expression and round only at the end, for presentation. If you round each line and then add up, the total can differ by cents from the rounded total. The numeric functions (ROUND, CEIL, FLOOR, TRUNC, ABS) are covered thoroughly in lesson 06-02.

This is where the logical order from 02-01 stops being theory. Try filtering by a calculated column:

The table exists, the alias is written right above, and yet the engine says that column doesn't exist. The explanation is in the diagram:

When WHERE is evaluated, the SELECT step hasn't run yet, so the name price_with_vat hasn't been created. When ORDER BY is evaluated, it has.

The two ways to filter by an expression:

And in ORDER BY the alias just works (lesson 02-05):

(First 5 of 20 rows.)

A summary worth memorising:

• Forgetting AS and losing a comma. SELECT name price FROM products doesn't fail: it returns the names under the header price. Always count the result's columns.
• An alias in single quotes. AS 'Price' is a syntax error in PostgreSQL. Double quotes for identifiers, single ones for strings.
• Expecting AS PriceVAT to keep the capitals. Unquoted it folds to pricevat.
• Using an alias in WHERE. column "..." does not exist. Repeat the expression or use a subquery.
• Forgetting the parentheses in the percentage margin. price - cost / price * 100 raises no error and returns meaningless numbers.
• Confusing the discount with the discounted amount. The amount charged is quantity * unit_price * (1 - discount); without the 1 - you compute exactly the opposite.
• Concatenating with || over columns that can be null. You lose the whole string. Use CONCAT or, better, COALESCE (module 6).
• Rounding too early. Round only in the presentation layer; intermediate calculations keep full precision.
• Tip: name your aliases like real columns. snake_case, lowercase, descriptive: price_with_vat, margin_pct, amount. "Pretty" aliases with quotes are only for the final report.
• Tip: validate the expression with SELECT and no FROM. SELECT 8 * 1.95 * (1 - 0.15); confirms in a second whether the formula is what you think.

Write a query on products that returns, with readable aliases: the product's name, its price, its unit margin in euros and its percentage margin rounded to one decimal. Explain why the parentheses in the percentage margin are essential.

On employees, build a single text column called profile with this exact format: Last name, First name (Job title) - City. For example: Alcázar Vives, Rosa (General manager) - Valencia.

On order_lines, return the line's id, the order it belongs to, the gross amount (before the discount), the discount in euros and the final amount charged, all three rounded to two decimals. Check your results against the six lines that have a discount.

Solution 1

(First 10 of 20 rows.)

The reasoning about the parentheses. The operator precedence from 01-03 says * and / are evaluated before -. Without parentheses, price - cost / price * 100 means price - ((cost / price) * 100). For product 1: 12.50 - ((7.80/12.50) * 100) = 12.50 - 62.40 = -49.90. A margin of -49.90 on a product that earns €4.70 a unit. The query runs without any warning: it's a silent error, the worst kind.

Solution 2

The reasoning. Columns and text literals alternate: the literals go in single quotes and carry the format's spaces, commas and parentheses inside them. All 8 rows came out because in this table none of the four columns used is null. If city were —and it could be, since the column allows nulls— that row would return NULL in its entirety. In a query destined for production it would be wise to shield it with COALESCE(city, 'Unassigned'), which you'll see in 06-04.

Solution 3

The six lines with a discount, out of the 47 the query returns:

The reasoning. The three columns share the base quantity * unit_price; what changes is what's done with discount: multiplying by discount gives what the customer saves, multiplying by (1 - discount) gives what they pay. The check that the formula is right is that discount_eur + final_amount = gross_amount on every row. On line 18 there's an apparent one-cent discrepancy (1.88 + 35.63 = 37.51 ≠ 37.50): it's the effect of rounding each column separately, since the exact values are 1.8750 and 35.6250. It's exactly why you round at the end and not at every intermediate step.

Now your queries don't just read: they compute.

• An alias with AS names any column or expression; without one you'll see ?column?. Write AS always, even though it's optional.
• Double quotes in an alias are only necessary if it contains spaces, symbols or capitals you want to keep: use them only for presentation aliases.
• Table aliases look superfluous with a single table and will be mandatory in module 3.
• You know how to build calculated columns: price with VAT, margin in euros and percentage margin, with the parentheses in the right place and without falling into integer division.
• You've got a grip on the expression that holds up the whole course: quantity * unit_price * (1 - discount), and you understand why the discount is a fraction and why the result comes out with four decimals.
• You concatenate text with || and you know its trap: any NULL operand wipes out the whole string. CONCAT avoids it, COALESCE (module 6) solves it properly.
• You present amounts with ROUND(expression, 2), rounding only at the end.
• And, above all, you understand from the logical execution order why an alias doesn't exist yet in WHERE and does in ORDER BY.

In the next lesson, Filtering Data with WHERE, you'll add step 2 of the diagram: you'll stop fetching all 20 rows of products or all 47 of order_lines and start asking only for the ones that meet a condition. With comparisons over numbers, text and dates, and with AND, OR, NOT and a few parentheses that, once again, will make the difference between a correct result and a silently wrong one.