Calculating in Queries

A query can show more than what's stored in the table. After SELECT, you can write a calculation, and the database works it out for every row. What would the games cost in a sale with $5 off each?

SELECT title, price, price - 5
FROM games;

You can add (+), subtract (-), multiply (*) and divide (/). A calculation can use a column and a number, as here, or several columns: in order_items, quantity * price is what the customer paid for all the copies of a game. As in math, * and / go before + and -, and you can use parentheses: price + 5 * 2 adds 10 to the price, while (price + 5) * 2 adds 5 and doubles the result. SQLite can even calculate without a table: SELECT 7 * 6; shows 42.

The calculated column isn't in the table, so it has no name of its own. The result calls it by the calculation, exactly as you typed it: price - 5. Write price-5 without spaces, and the column is called price-5. In the next exercise, you'll learn to give it a better name.

The calculation changes only what the query shows. The table stays as it was: after this query, the games cost just what they cost before. (You'll learn how to change the data in a table later in the course.)

Numbers with a decimal point work too: price * 0.8 is the price after a 20% discount. The result is then a decimal number, and SQLite shows it as 32.0 even when it's whole. Be careful with dividing, though: when both numbers are whole, the result is whole too, so 25 / 2 gives 12, not 12.5. You'll learn why, and what to do about it, in lesson 4.

Typical mistake: multiplying with x. In SQL, as in most programming languages, the sign for multiplication is the star: price * 2. SELECT title, price x 2 FROM games; ends with the error near "2": syntax error, because SQLite reads x as a new name for the column price, just like with the missing comma in the first lesson.