AI Hints
Homeโ€บSQL Tutorialโ€บCalculations and Labels
SQL ยท Lesson 20 of 126

Doing Maths in SELECT

New columns from old ones.

โฑ 2 min readโ–ถ Video at 28:07๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

Helen asks, how much money is sitting on my shelves? For each book, that's the price, times the number in stock. There's no column for that. But SQL can calculate it for you, on every row.

Doing Maths in SELECT
From the video: Doing Maths in SELECT
  • Money sitting on the shelves
  • Price times stock
  • Calculate it on every row

A calculated column

You can put a calculation right after select, like price times stock. SQL works it out for each row. Run it. It works, but look at the column heading. It's just the formula, price star stock. That's not very friendly.

query.sqlsql
SELECT title, price * stock
FROM books
LIMIT 3;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚       title        โ”‚ price * stock โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ The Silent Orchard โ”‚ 869.42        โ”‚
โ”‚ Code of the River  โ”‚ 337.74        โ”‚
โ”‚ Stars Over Lagos   โ”‚ 469.53        โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Give it a name with AS

To give a column a friendly name, add as, and then the name you want. This is called an alias. Now we can also sort by that name. Run it. SQL in a Weekend holds the most money on the shelves, at almost thirteen hundred dollars.

query.sqlsql
SELECT title, price * stock AS stock_value
FROM books
ORDER BY stock_value DESC
LIMIT 3;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚          title           โ”‚ stock_value โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ SQL in a Weekend         โ”‚ 1289.57     โ”‚
โ”‚ A Short History of Bread โ”‚ 1059.47     โ”‚
โ”‚ Numbers Don't Lie        โ”‚ 1033.53     โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Maths signs

Here are the maths signs you can use. Plus and minus. A star for multiply, and a slash for divide. And the percent sign, which gives the remainder after dividing. Just like a calculator.

keyvalue
+ -add, subtract
*multiply
/divide
%remainder

Summary

You can calculate new columns, and name them with as. Next, rounding, and a small surprise with division.

Key takeaways
  • Calculate in SELECT
  • AS gives a friendly name
  • + - * / %
Next up: Rounding and Division โ€” tidy numbers