Does the ROUND() function round up?

Frequently Asked Questions SQL FAQ
1

Question

Does the ROUND() function round up?

Answer

When using the ROUND() function, you can provide a second argument, which is the precision, or number of decimal places to round the number on.

In SQLite, rounding is done by rounding up if the next decimal value is 5, and rounds down if the value is less than 5.

For example,

/* This will result in 4.0 */
SELECT ROUND(3.5, 0);

/* This will result in 6.4 */
SELECT ROUND(6.42, 1);

/* This will result in 6.0 */
SELECT ROUND(6.42, 0);
14 Likes
2

porqué esta sintaxis es correcta? ROUND(avg(downloads)), no necesitaría el segundo valor correspondiente a redondeo?

6 Likes
3

Si el segundo argumento de la funciĂłn ROUND es omitido, se asume como 0, es decir, ROUND(AVG(downloads)) es equivalente a ROUND(AVG(downloads), 0)

10 Likes
4

why entering the following results in 2.03?

select avg (round (price, 0))
from fake_apps
;

2 Likes
5

You are using round function for values from the rows and then using AVG function. If you want to round the result of AVG function then use it more like ROUND(AVG()).

6 Likes
6

SELECT ROUND(price, 2) FROM fake_apps
WHERE price = (SELECT AVG(price) FROM fake_apps);

Does this is right? Why it no results return?

3 Likes
7

You need to put average price in the ROUND brackets, instead of regular price, like you did in the first line.

If you do only the first line of your code you will get a column with rows of rounded prices of all the apps on the list.
When you add the second line, it will return you a result only if the price will have the average value, what will never happen.

Can you see the mistake ?

6 Likes
8

You’re right. Thank you bro.

1 Like
9

Why doesn’t it work even if I modify it to SELECT ROUND(SELECT AVG(price) FROM fake_apps,2) as round_value;?

1 Like
10

I’m only as far as you in this course, but I believe you only use SELECT once in a query. Removing the second SELECT makes your query shorter, and also makes it correct! No need for the extra SELECT.
SELECT is to pick out rows from a table, so in my understanding, we only pick out rows once per command, although we can stack criteria and functions to affect that selection.

11

I am interested to just averaging the apps which prices are not zero.

Tried using
SELECT AVG (SELECT price FROM fake_apps WHERE price != 0;)
FROM fake_apps;

But no results. Can anybody help?

2 Likes
12

Why would you use a nested/sub query? In a generic expression, simply using WHERE:

SELECT fields FROM table WHERE condition

only results in the rows which meet the where constrain.

1 Like
13

How can we round to the nearest ten or hundred ?
(For exemple “downloads” column).

14

then second argument of the round() function specifies the number of decimals.

15

Ok, I see, is there any way to round to the nearest hundred (for ex.)?

16

Can you give en example? Do you mean like so:

345.555566 => 345.556

or:

345.555566 => 300

?

17

Yes right like in the second exemple : 345.555566 => 300 !

18

You would need to do something more complicated like mod:

https://stackoverflow.com/questions/8839868/mysql-query-to-get-floor-or-round-to-next-lowest-100-unit-hundred-block-n

seems what you want to do, is not a common use case. Or at least not at database level.

19

Ok, I was just curious because when we work with large numbers, it might be useful.
And on the same logic, I taught that if you write round(number; negative integer) would round up to ten, hundred etc. But it didn’t work so easily.
Thank you for your answers :slight_smile:

20

no, round() is really designed for working with decimal numbers.