ROUND, ABS and CAST
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — ROUND
Codemon's weights were measured with decimals. ROUND(x, n) keeps n decimals — none when n is omitted:
SELECT codemon, weight_kg,
ROUND(weight_kg, 1) AS one_decimal,
ROUND(weight_kg) AS whole
FROM sightings
LIMIT 4;You already used ROUND on averages; it matters even more when the data itself has decimals:
SELECT ROUND(AVG(weight_kg), 2) AS avg_weight,
ROUND(SUM(weight_kg), 1) AS total_weight
FROM sightings;ROUND returns a REAL
ROUND(7.0) gives 7.0, not 7, and ROUND(4.5) gives 5.0 (halves round away from zero). To get an integer, combine with CAST, coming up in this chapter.
Exercise 2 — ABS: distance, not direction
ABS(x) removes the sign. It turns a difference into a distance — here, how far each weight is from the 10 kg of the field guide:
SELECT codemon, weight_kg,
ROUND(ABS(weight_kg - 10), 2) AS gap
FROM sightings
ORDER BY gap
LIMIT 3;Without ABS, Codémons lighter than 10 kg would get negative gaps and sort before the closest one.
A few more examples:
SELECT ABS(-3) AS a, ABS(3) AS b, ABS(2.5 - 7) AS c;Exercise 3 — CAST: changing the type
Column level_text holds numbers typed as text — and sometimes no number at all. Sorting text is alphabetical: '9' comes after '14':
SELECT level_text
FROM sightings
WHERE level_text NOT IN ('', 'unknown')
ORDER BY level_text;CAST(value AS INTEGER) converts the text into an actual number, and the sort becomes numeric:
SELECT level_text, CAST(level_text AS INTEGER) AS level
FROM sightings
WHERE level_text NOT IN ('', 'unknown')
ORDER BY level;Garbage in, zero out
CAST('unknown' AS INTEGER) and CAST('' AS INTEGER) give 0, silently. Filter such values out before averaging, or they drag the result down: with them, the average level is 8.0 instead of 11.0.
Exercise 4 — Integer division
What does SELECT 7 / 2, CAST(7 AS REAL) / 2 return?
Hint : Two integers divide into an integer; CAST one side to REAL to keep the decimals.
Exercise 5 — Average level, properly
Complete the query so that it returns the average reported level, ignoring the rows where the level is missing ('' or 'unknown').
Hint : Convert the text to a number, and keep only the rows that are not missing.
Exercise 6 — Numbers in, numbers out
Match each expression with its result.
ROUND(12.333, 1)ROUND(4.5)ABS(-2.5)CAST('12' AS INTEGER) + 112.35.02.513Exercise 7 — Not a number
What does CAST('unknown' AS INTEGER) return in SQLite?
0NULL'unknown'Hint : SQLite converts what it can and gives up silently.
Exercise 8 — Closest to the guide
The field guide gives 10 kg as the typical weight of an adult Codémon. Professor Pi wants the sightings closest to it.
Return codemon (as typed), weight (weight_kg rounded to 1 decimal) and gap_to_guide (the absolute difference between the weight and 10, rounded to 1 decimal). Sort by gap_to_guide ascending and keep the first 5 rows.
-- Write your query below