# FAQ: Aggregate Functions - Round

This community-built FAQ covers the “Round” exercise from the lesson “Aggregate Functions”.

Paths and Courses
This exercise can be found in the following Codecademy content:

## Join the Discussion. Help a fellow learner on their journey.

Agree with a comment or answer? Like () to up-vote the contribution!

Found a bug? Report it!

Have a question about your account or billing? Reach out to our customer support team!

None of the above? Find out where to ask other questions here!

I still don’t understand why the numeral (2) is used to round to an average of two decimal places.

``````SELECT ROUND(price, 0)
FROM fake_apps;
``````

The above example rounds to the nearest integer.

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

The above example rounds to the nearest second decimal place.

``ROUND(123.456, 2)    //  123.46``

Question:

If the function takes in a column name, how does avg(prices) qualify as one? I thought avg(prices) returns a single value?

Can someone elaborate. Thanks

Why does it matter which order you put the functions?

For example,

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

returns the correct answer of 2.02, but:

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

returns 2.02365, with the wrong number of decimals.

Why doesn’t the second set of commands take the column of prices, round it to two decimals, and then average it? I suppose this second approach introduces more error, but the commands should still work, no?

5 Likes

My understanding is that `avg(prices)` actually creates a new column (temporarily, I guess) with just one row whose value is the average price.

In the first line

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

How would I know the exact order / way in which the parentheses are used? I mean instead of (AVG(price), 2) why not AVG(price, 2)? How / where can I learn; why is it the way it is?

Thank you for the help

don’t read letters/characters, read it as a set of instructions: calculate the the average price and round this result to two decimals

understanding functions, parameters and arguments are the underlying programming concepts you need/are looking for.

My code worked, but would it work in a real-world scenario?

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

It is not returning the ‘wrong’ number of decimals. It is returning the exact result of the AVG function.
Your suggested function takes the column of prices, rounds each price to 2 decimals, and then averages the rounded prices. There is no reason to expect that the average of a set of two decimal numbers will have exactly two decimals.
For example, if I have just two prices: 1.12 and 2.01, then the ROUND function will first round them to two decimal places, resulting in 1.12 and 2.01, because they are already stated at 2 decimal places. The AVG function will then calculate the exact average of 1.12 and 2.01. The formula for AVERAGE of two numbers is: (A + B) / 2
(1.12 + 2.01) / 2 = 3.13 / 2 = 1.565
In this example, the exact return of the AVERAGE function has 3 decimal places.

Has anyone got any good resources to understand and generalize the underlying concept of why fundamentally the below get the right answer. Trying to learn the fundamentals to be more intelligent in my approach

“SELECT ROUND (AVG (PRICE), 2)
FROM Fake_apps
;”

Let’s exhaust your abilities before shopping for more resources so we know what to look for. Sound good?

First, what does our table look like?

There are 200 rows of data. The column we are querying is `price`, albeit we don’t want the whole column, just the average price. The table from our query will have a single value.

This is the role of an aggregate function. Apply the function to a column of data and return a value. Well, one role. In this example we’re after one number. The average price given a column of 200 values.

The role of a SELECT statement is pretty clear. Tell SQL to sit up. Parameters and clauses are following… All we need is a table to act upon.

Remember the statement,

``````SELECT * FROM fake_apps;
``````

?
That will return the complete table. The wildcard will bind to all the columns.

If we only want to see one column,

``````SELECT price FROM fake_apps;
``````

Once the column is confirmed we can narrow this down to the singular value we seek, the average.

``````SELECT AVG (price) FROM fake_apps;
``````

We have still selected the entire column, except this time we have handed it to the AVG() function to return a table with only one row, one column, one value. Essentially, we have short-circuited the normal building process and will now produce a 1 X 1 table, and populate it with the computed value returned by the function.

The final step is only acting on the return value of the AVG() function. The column table is already out of the picture.

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

The second positional argument, `2` denotes the precision. Our reported value will be in dollars and cents (tendered currency format).

If there is anything we covered above that is not clear, then search is your friend. That’s how we would find the resources you are looking for.

Above will produce a table with no column heading. SQL used our function expression as the heading by default. To round out our report we can supply the missing heading…

``````SELECT ROUND (AVG (price), 2) AS 'Average Price' FROM fake_apps;
``````

For more fun we can invoke another aggregate function on our column query…

``````SELECT ROUND (AVG (price), 2) AS 'Average Price', COUNT (*) AS 'Rows' FROM fake_apps;
``````

Note how this time the wildcard is scoped to the rows, not the columns.

``````SELECT
ROUND (AVG (price), 2) AS 'Average Price',
MIN (price) AS 'Lowest Price',
MAX (price) AS 'Highest Price',
COUNT (*) AS 'Rows'
FROM fake_apps;
``````

2 Likes

Thank you for this! - this was really helpful and I appreciate you taking the time to share this

1 Like

Is there a way to round down with round function?

if have 4.4, `round()` will round down to 4. If you always want to round down (even with .5 or higher), use the `floor` function

We are just learning about “nesting” (sorry im a little bit of an excel junkie lol) aggregates inside of each other in SQL (example, SELECT ROUND(AVG(price), 2)). My question is can this work for other aggregates? Example if I wanted to round, pull an average and display the min and max of all the same column, can they all be written together?

what you could do is:

``````SELECT MAX(price), MIN(price), ROUND(AVG(price), 2))
``````

we can simple select the same column multiple times, and call a different function. Then using aliases for your columns would be a nice touch