FAQ: Aggregate Functions - Group By II

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 “Group By II” 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 Group By II

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 did not understand why I need the round to be on the “group by” and on the “order by”. I made some tests without it and the return was the same.

Examples:

select round(price), count(name)
from fake_apps
group by price
order by price;

same result as:

select round(price), count(name)
from fake_apps
group by 1
order by 1;

Anyone? thx :slight_smile:

3

If, for legibility, it’s nice to capitalize statements (SELECT, ROUND, etc.), why would one want to replace a column reference with a number? Doesn’t this make it harder to read?
e.g.
SELECT category,
price,
AVG(downloads)
FROM fake_apps
GROUP BY category, price;
becomes
SELECT category,
price,
AVG(downloads)
FROM fake_apps
GROUP BY 1, 2;

1 Like
4

gafanhoto1990, I believe that the round in “GROUP BY ROUND(imdb_rating)” is not to round but to refer to the exact same name of that column.
As an alternative, you could add “AS ‘Avg.’” in the select, in which case you would use only “GROUP BY ‘Avg.’”.

But I do have a question:
How to ROUND to the tens or hundreds. I understand that rounding to the 1st decimal would be ROUND(column,1), but why I cannot use ROUND(column,-1) to round to the tens?

5

I want to make sure I understand this statement correctly:

SELECT category, 
   price,
   AVG(downloads)
FROM fake_apps
GROUP BY category, price;

The part I’m unsure about is the AVG(downloads). If I delete the GROUP BY from this statement, the result is just one line:

category price AVG(downloads)
News 0.0 16613.8
  1. That makes sense about the AVG(downloads) value, it’s the average of all the apps in the table - but why, of all the prices, is 0.0 displayed, and why “News” of all the categories?
  2. With the GROUP BY, the value for AVG(downloads) changes: when grouped by 1, 2, all the price groups of all categories are shown. The AVG refers to each price group out of each category. When grouped just by 1, the value changes to reflect the average of each category.

What I’m concerned about is how the syntax works: GROUP BY actually seems to change what the argument of AVG() is. Does anyone else feel this is a weird kind of a jump?
Wouldn’t it make more sense to have price groups as arguments of AVG()?

1 Like
6

Came over from the Code Challenge 5, where it seems quite a few of us tried to combine HAVING function with column reference and a sum function. Tought this lesson section may have an answer.

Question: Can column references only be used in GROUP BYs?

… because this does not work:

SELECT user_id, SUM(watch_duration_in_minutes) AS ‘duration’
FROM watch_history
GROUP BY 1
HAVING 2 > 400;

7

Can somebody help me understand the meaning of values in the price column, if we do not group by price.

SELECT category, price,
  AVG (downloads)
FROM fake_apps
GROUP BY 1;

p.s. apologies for the formatting of this question, first time user here

8

Maybe I am miss understanding ‘column reference’

In this statement is says " Write the exact query but use column reference numbers instead of column names after GROUP BY .

When you perform a
select *
from fake_apps;
You will see that category is column 3 and price is column 5. Yet, the solution is to use (1,2)

select category,
price,
avg(downloads)
from fake_apps
group by 1, 2;

I must be missing something in this case with the column referencing.

9

Question:
I understand the question and know the answer. But I’m wondering if I don’t group it by price, is the result price max, min, or avg?

SELECT category, 
   price,
   AVG(downloads)
FROM fake_apps
GROUP BY 1;