Online College Courses for Credit

+
ROUND to Round Numbers

ROUND to Round Numbers

Rating:
(0)
Author: Sophia Tutorial
Description:

Compose a query that utilizes the ROUND function to display numeric values from a given column in a more readable format.

(more)
See More
Fast, Free College Credit

Developing Effective Teams

Let's Ride
*No strings attached. This college course is 100% free and is worth 1 semester credit.

47 Sophia partners guarantee credit transfer.

299 Institutions have accepted or given pre-approval for credit transfer.

* The American Council on Education's College Credit Recommendation Service (ACE Credit®) has evaluated and recommended college credit for 33 of Sophia’s online courses. Many different colleges and universities consider ACE CREDIT recommendations in determining the applicability to their course and degree programs.

Tutorial

what's covered
This tutorial explores using the ROUND function to round numbers.

Although we did take a look at the ways to cast values to numeric values, this is a feature that doesn’t exist in all databases. The ROUND function is one that does exist in most databases that rounds a numeric value to the nearest integer or within a number of decimal places.

The ROUND function takes in two arguments, the first is the number, column or expression to be rounded and the second one is the number of decimal places after rounded. If you do not include the second argument, it defaults to 0, meaning that it will round to the nearest integer.

Let us run a quick script to make some updates to the track table so that we can take a further look at how the ROUND function works:

UPDATE track SET unit_price = .49 WHERE track_id = 1;
UPDATE track SET unit_price = .51 WHERE track_id = 2;
UPDATE track SET unit_price = .55 WHERE track_id = 3;
UPDATE track SET unit_price = .01 WHERE track_id = 4;
UPDATE track SET unit_price = .91 WHERE track_id = 5;
UPDATE track SET unit_price = .95 WHERE track_id = 6;

We can now take a look at what this would look like ordered based on track_id. The LIMIT clause can be used to only display the first 6 rows.

SELECT track_id, unit_price
FROM track
ORDER BY track_id
LIMIT 6;

File:11312-1360-1.png

Let’s take a look at what this would look like if we rounded based on various parameters:

SELECT track_id, unit_price, ROUND(unit_price), ROUND(unit_price,1),ROUND(unit_price,2),ROUND(unit_price,3)
FROM track
ORDER BY track_id
LIMIT 6;

File:11313-1360-2.png

The first round is based on the closest integer. Any value of 0.50 or higher would be rounded to 1 whereas less than 0.50 would be rounded to 0.

The second round rounds to the closest tenth. Most of this will be obvious but it is important to also look at how the round works in the 6th row to round up to 1.0.

The third round rounds to the nearest hundredth. No changes with the data appear here.

The fourth round is an interesting one as it rounds to the nearest thousandth. Although the data is stored with only 2 decimal places, as you’ll see, it can add that extra decimal to keep values consistent.

Where the round can be most useful is within calculations like those that use the AVG function as the data can be a bit complex.

For example, if we want to get the average total of invoices for each customer, the query would look like the following:

SELECT customer_id, AVG(total)
FROM invoice
GROUP BY customer_id
ORDER BY customer_id;

The result would look like the following:

File:11314-1360-3.png

Being that the total is based on currency, it makes sense to round it to the nearest cent (2 decimal places).

We could do that by adding the ROUND function around the AVG function.

SELECT customer_id, ROUND(AVG(total),2)
FROM invoice
GROUP BY customer_id
ORDER BY customer_id;

File:11315-1360-4.png

try it
Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next, try your own choices for which columns you want the query to provide.

summary
The ROUND function can be used to round numbers, columns and calculations to the nearest decimal or integer.