FAQ: Aggregate Functions - Round

Frequently Asked Questions SQL FAQ
1

Community%20FAQs%20on%20Codecademy%20Exercises
Community%20FAQs%20on%20Codecademy%20Exercises1000×208 141 KB

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:

Web Development
Data Science

Learn SQL

FAQs on the exercise Round

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

Ask or answer a question about this exercise by clicking reply (reply) below!

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

Need broader help or resources? Head here.

Looking for motivation to keep learning? Join our wider discussions.

Learn more about how to use this guide.

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!

2

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

3 
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
4

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

5

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?

4 Likes
6

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

7

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

8

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.

1 Like
9

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

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

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.

11

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
;”

12

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?

fake_apps_schema

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).

prices_avg_query_result

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.

Addendum

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;

average_price

Post Addendum

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;

average_price_rows

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

Adding to the report parameters…

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

lots_of_aggregate_functions

2 Likes
13

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

1 Like
14

Is there a way to round down with round function?

15

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

16

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?

17

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