FAQ: Code Challenge: Aggregate Functions - Code Challenge 5

This community-built FAQ covers the “Code Challenge 5” exercise from the lesson “Code Challenge: Aggregate Functions”.

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

Web Development
Data Science

FAQs on the exercise Code Challenge 5

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!

Confused why my query isn’t working here:

I think it is because I am trying to call on the second column as 2. Other than that, my query is the same as the hint. Are there circumstances where you can’t call on the column number like this?

10 Likes

I got the exact same problem, I’m guessing that when you want to use actual values of number (in this case 400)other than notation related by a number(like 2 in this incident) in the same sentence, it doesn’t work. Since ‘Group by 1’ works just fine.

But I might be wrong.

Just wondering why using the clause AS is necessary to get a Query result?
i.e This will get me nothing:
SELECT user_id,
SUM(watch_duration_in_minutes)
FROM watch_history
GROUP BY 1
HAVING(watch_duration_in_minutes) > 400 ;

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

Works perfectly. From what I can see AS renames a column but why would that change me receiving the data? Is it because without a column for it to place data it gets confused?

1 Like

you forgot in your first solution after HAVING the sum aggregate. in the second solution it is included with the alias name

1 Like

same here. it must be work, but how? some pro here?

Noob here, but I believe we can’t use the alias “2” in place of SUM(watch_duration_in_minutes) because the function…

HAVING 2 > 400

…attempts to process those numbers as values rather than an alias placeholder, which produces an error.

I read just now that basic SQL allows alias references only in ORDER BY commands, and that the HAVING clause is processed before the SELECT clause. Therefore, attempting to use an alias reference number in a HAVING clause produces an error because the column the user wants to reference has not yet been processed and assigned that numerical value . . . and even then, I think the system might not have a clear-cut way of differentiating whether you’re intending the number 2 to be an alias reference or a number interacting with the column you’re numerically assessing.

I don’t know the logic as to why, but perhaps this Codecademy course will make it clear in time. Thus far the course has not mentioned that the lines of code are not necessarily processed left-to-right, top-to-bottom, despite the linear learning method suggesting otherwise.

9 Likes

Why when I add ORDER BY code is not returning results?

SELECT user_id, SUM(watch_duration_in_minutes)
from watch_history
GROUP BY 1
ORDER BY 2 DESC
HAVING SUM (watch_duration_in_minutes) > 400;

Hi, could you please help me to understand why there are two different results for the following queries when having or not single quotes after the HAVING clause?

Query 1 with (’ '):

SELECT user_id, 
   SUM(watch_duration_in_minutes) AS 'total_duration'
FROM watch_history
GROUP BY 1
HAVING 'total_duration' > 400;

Query 2 without (’ '):

SELECT user_id, 
   SUM(watch_duration_in_minutes) AS 'total_duration'
FROM watch_history
GROUP BY 1
HAVING total_duration > 400;
2 Likes

Noob here I have the same code as you, I have two columns returned, the id and the duration, but it doesn’t filter out greater than 400mins?
It doesn’t like it if you try to use DESC either?
Any thoughts please?

It’s ok I’ve figured it out, I was missing an underscore between my total and duration. Works fine now.

                                                             SPOILER ALERT

I came across one thing

No Result

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

Correct

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

As you can see the GROUP BY and HAVING commands are switched in order.
In the first code the GROUP BY is below the HAVING and Vice versa. I was wondering what makes that happen. does SQL Not identify the GROUP BY if it is written after the HAVING command, The HAVING command seems not recognizing the GROUP BY if it is written Afterwards? and does the HAVING and similar commands need the arguments written before for them to be able to take in the arguments?

I got exactly the same Problem. Any answer by codecademy?

Same here. Why is “HAVING 2 > 400” not working here?

This is a guess. I believe, you don’t need to put total_duration in quotations because you are calling a column, not a text.

select user_id, sum(watch_duration_in_minutes)

from watch_history

group by 1

having 2 > 400;

Plz anyone help me with this, what’s wrong here? especially last line.

I’m wondering why we Group By user_id here. A user ID is unique, so how can you form it into a group?

Yeah, I am facing the exact same problem here. I type the SUM(watch_duration_in_minutes) in the second line. Hoping not to confuse it. Still does not work

Can someone explain why in this scenario I could not use 2 to replace SUM(watch_duration_in_minutes). The code would only work if used the whole line even though it should be the same.

I’m having the same issue. the solution provided is:

SELECT
  user_id,
  sum(watch_duration_in_minutes) AS time
FROM watch_history
GROUP BY user_id
HAVING time > 400;

The code I typed in is:

select 
  user_id,
  sum(watch_duration_in_minutes) AS duration
from watch_history
group by user_id
having duration > 400;

The exact same code with the exception of the re-named “duration” column.

But mine was “wrong” and would not return results, where as the “correct” solution worked fine. This seems more like a bug.