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

Rounding and Division

Tidy numbers.

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

Helen is planning a twenty percent off sale on fantasy books. The sale price is the price times zero point eight. But that can give long, messy decimals. Prices need exactly two decimal places.

Rounding and Division
From the video: Rounding and Division
  • 20% off fantasy books
  • Price times 0.8
  • Prices need two decimals

ROUND

Round takes a number, and how many decimal places to keep. Round price times zero point eight, to two places. Notice we also split the select list over several lines, one column per line. That's easier to read when it gets long. Run it. Dragons of Ember Bay drops from seventeen ninety nine to fourteen thirty nine.

query.sqlsql
SELECT title,
       price,
       ROUND(price * 0.8, 2) AS sale_price
FROM books
WHERE genre = 'Fantasy';
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚        title         โ”‚ price โ”‚ sale_price โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ Dragons of Ember Bay โ”‚ 17.99 โ”‚ 14.39      โ”‚
โ”‚ Crown of Ash         โ”‚ 16.99 โ”‚ 13.59      โ”‚
โ”‚ The Glass Forest     โ”‚ 18.49 โ”‚ 14.79      โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

A division surprise

Now a surprise that trips up beginners. Seven divided by two should be three and a half. Run it. SQL says three! When both numbers are whole numbers, the answer is a whole number too. Write two point zero instead, and you get three point five. The percent sign gives the remainder, so ten percent three is one.

query.sqlsql
SELECT 7 / 2      AS whole,
       7 / 2.0    AS exact,
       10 % 3     AS remainder;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ whole โ”‚ exact โ”‚ remainder โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 3     โ”‚ 3.5   โ”‚ 1         โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Summary

Round tidies numbers, and dividing whole numbers drops the decimals, so add point zero when you need them. Next, a way to turn numbers into words.

Key takeaways
  • ROUND(x, 2)
  • Whole / whole = whole
  • Use 2.0 for decimals
Next up: Labels with CASE โ€” if this, then that