Arithmetic in a query
In short: In SELECT you can calculate with columns (+, -, *, /) for every row and round with ROUND, but in SQLite, PostgreSQL and SQL Server dividing two integers drops the fractional part.
Problem
What does this line cost?
Accounting wants to know, for every order line, what it costs: the quantity times the price. The order_items table has only the two numbers:
Order 3 has apples, 4 at 2.10 €, and laundry powder, 3 at 12.50 €. Working it out by hand for 152 lines is not a job for a person. And keeping the total in yet another column means keeping it correct after every change.
The idea
A column that is computed
Besides columns, SELECT can contain expressions: calculations with the columns. The database computes them for every row:
The operators are +, -, *, / and % (the remainder of a division). The computed column is named after the expression itself, so you give it a short alias with AS. The table does not change: the calculation exists only in the result.
You can also sort by the alias. The five most expensive lines across all orders:
An expression works without a table, too. Then SELECT computes it once, like a calculator. Multiplication comes before addition, and brackets change the order:
Try it
The price with VAT
The prices in the database are without VAT. Show the name, the price and the price with 20% VAT in a column called with_vat.
Solution
SELECT name, price, price * 1.2 AS with_vat FROM products;price * 1.2 AS with_vat. With 20% on top, the price is 120% of the old one, that is 1.2 times. White bread comes out at 1.44 €. Here the check also looks at the column names.
See it
The rating as a percentage
The website wants to show the ratings from 1 to 5 as percentages: 5 is 100%, 4 is 80%. Run:
This is integer division. When both numbers are whole, the result is whole as well:
As soon as one of the numbers is a fraction (2.0), or is turned into one with CAST(… AS REAL), the division keeps the fraction. For the percentages it is enough to multiply first, by 100.0:
With price this trap does not happen: the price is a fractional number (REAL). Watch out for columns holding whole numbers, like rating, quantity and stock.
More precisely, if you are curious
PostgreSQL and SQL Server divide whole numbers the same way: 5 / 2 is 2. MySQL does the opposite: there 5 / 2 is 2.5000, and integer division has its own operator, DIV. Division by zero gives NULL in SQLite and an error in PostgreSQL.
The idea
ROUND: to the cent
A promotion: 15% off dairy. The new price is 85% of the old one:
Nobody can pay 0.935 € for a yoghurt. ROUND(x, 2) rounds to two digits after the point, that is to the cent. ROUND(x) rounds to a whole number.
Look at the white cheese: 6.715 became 6.71, not 6.72. To see why, look below.
More precisely, if you are curious
Fractional numbers in a computer are stored in binary, and in binary 0.1 has no exact form, just as 1/3 has no exact form in decimal digits. That is why:
The screen shows 0.3, but the comparison says “no” (0). printf with %!.20f shows what is really stored. The white cheese is the same story: 7.9 × 0.85 is 6.71499… in memory, and ROUND honestly rounds it down.
That is why real systems do not keep money as REAL. They keep it in whole cents (1.20 € is 120), or in a type for exact decimal numbers: NUMERIC(10, 2) or DECIMAL(10, 2) in PostgreSQL, MySQL and SQL Server. SQLite has no such type, so cents are the way to go there:
In cents, 790 × 85 is exactly 67,150, and dividing by 100 gives 671.5 cents, which round to 672, that is 6.72 €.
Try it
The promotion
Show the name, the price and the sale price of every product. The sale price is price * 0.85, rounded to the cent, in a column called sale_price.
Solution
SELECT name, price, ROUND(price * 0.85, 2) AS sale_price FROM products;ROUND(price * 0.85, 2) AS sale_price. ROUND wraps the whole expression: first it is computed, then rounded.
Exercise
Two tasks
1. The insurer asks what the goods in the warehouse are worth. Show the five products with the largest stock value (price times stock), largest first. The gift card is not goods on a shelf: keep only the products that have a category.
Name, stock and stock_value, the five largest:
Solution
SELECT name, stock, price * stock AS stock_value
FROM products
WHERE category_id IS NOT NULL
ORDER BY stock_value DESC
LIMIT 5;price * stock AS stock_value, WHERE category_id IS NOT NULL, ORDER BY stock_value DESC LIMIT 5. Coffee beans come first: 15 packs at 14.90 € are 223.50 €. Here the check also looks at the order.
2. Goods arrive in boxes of 12. For every product with a category that is in stock, show how many full boxes there are (boxes) and how many items are left outside them (loose).
Name, stock, boxes and loose:
Solution
SELECT name, stock, stock / 12 AS boxes, stock % 12 AS loose
FROM products
WHERE stock > 0 AND category_id IS NOT NULL;stock / 12 AS boxes, stock % 12 AS loose. Here integer division is exactly what you want: 40 loaves of bread are 3 full boxes and 4 loose ones.
Test