Does the ROUND() function round up?

FAQ SQL FAQ
#21

Would it work if you just added another column, lets say ‘10^2’, or ‘x100’, then assigned that value as new column value = old column value / 100?

it would convert the 345.555566 = 3.45555566 , then you could apply the same round(value, 0) = 3.00000000000000000000000. You could then also reconvert and assign another column where that *100 = 300.000000000000 (this is a very expensive way of doing this, but I have performed similar functions in VBA when needing to round up to the nearest 5 multiple… if (int(x/5)=round(x/5),int(x/5)*5,int(x/5)*5+5) for instance.

Not sure if that helped at all, but I have encountered a purpose in real life in other projects.

#22

Why doesn’t it work?

SELECT AVG(price) AS ap
ROUND(ap, 2)
FROM fake_apps;
#23

if you want round up value then better you use CEILING() function.

#24

When you use ‘AS’ is to rename the columns default name, not to assign as a variable. If you want to ROUND the average price you can use the following query:

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

The result will be: 2.02 and the title will be ROUND ( AVG(price), 2 )
image

If you want to rename that title with AS you can use a query like…

SELECT ROUND ( AVG (price),2 ) AS ‘ap’

FROM fake_apps;

The result will be: 2.02 and the title will be ap
image

see you @potaton2

#25

Hello All,

i just wanted add that you can also get rounded value of the average distinct value of a column:

eg :
SELECT ROUND(AVG(DISTINCT price ),2) FROM fake_apps;

1 Like
#26

What is the difference between ROUND(smth, 0) and ROUND(smth, 1). In both cases it shows one digit after decimal. Why i cannot round to whole number ( ROUND(smth, 0) should do it ) ?

#27

I was wondering the same thing: did not get the chance to dig into documentation yet, but here is what I noticed.

SELECT ROUND(AVG(DISTINCT price), 1)
FROM fake_apps;

This returns 4.2.

SELECT ROUND(AVG(DISTINCT price), 0)
FROM fake_apps;

This returns 4.0.
I was expecting to get a clean 4, without the .0 decimal.
I guess it does not really matter, but I am still curious why SQLite’s output keeps a .0 decimal after the number. If anyone knows, I’m all ears.

1 Like
#28

I think that is because the data type of the column price is REAL and not INTEGER. Values with data type as REAL are always denoted with a decimal.

1 Like
#29

The reason is because you forgot to put single quotes around your “AS” value AND you also forgot to separate your sub-query properly

SELECT ROUND((SELECT AVG(price) FROM fake_apps),2) AS 'round_value' FROM fake_apps;