🎓 A1Learner
🔍

SQL Number Functions

Number functions are functions that work on numbers. ROUND and ABS are two common number functions.

Rounding prices

ROUND first appeared in the Sum and Avg lesson, and this lesson gives it a full pass. ROUND(price, 1) keeps one decimal place.

SELECT name, ROUND(price, 1) AS rounded_price
FROM products
ORDER BY product_id;

Dropping the minus sign

The raw_gap column subtracts 50 from each stock count. The gap column wraps the same subtraction in ABS, and every minus sign is gone.

SELECT name, stock - 50 AS raw_gap, ABS(stock - 50) AS gap
FROM products
ORDER BY product_id;

One function inside another

ROUND wraps AVG in this query. AVG runs first, and ROUND cuts its long decimal to two places.

SELECT ROUND(AVG(price), 2) AS average_price
FROM products;

Example

SELECT category, ROUND(AVG(price), 2) AS average_price
FROM products
GROUP BY category
ORDER BY category;

Key points

  • ROUND: a function that cuts a number to the decimal places named
  • ABS: a function that returns a number without its minus sign
  • ROUND first appeared in the SQL Sum and Avg lesson.
  • Use ABS to read a difference as a plain distance.
  • A function can wrap another function.
  • The inner function runs first.

In this example, GROUP BY gathers the products into one group per category. AVG works out the average price inside each group. ROUND wraps AVG and cuts each average to two decimal places. ORDER BY sorts the groups by category name.

Try it Yourself

Edit the code, then press Run.

Running is simulated · DB Fiddle

Exercise

What does ABS(-8) return?