FAQ: Code Challenge: Aggregate Functions - Code Challenge 3

FAQ SQL FAQ
#3

I was able to complete the challenge, but I was curious why COUNT(watch_duration_in_minutes) works and not COUNT(duration). As far as I understand, the ROUND command doesn’t actually change the data in the table, so how was COUNT able to count the rounded values when I specified the original column name, and not the renamed round results?

In other words, why is the below example right?
Correct:
SELECT ROUND(watch_duration_in_minutes, 0) AS duration, COUNT(watch_duration_in_minutes) AS “count” FROM watch_history;

Not correct:
SELECT ROUND(watch_duration_in_minutes, 0) AS duration, COUNT(duration) AS “count” FROM watch_history;

1 Like
#4

Completely agree with your solution. Not sure why it was shown incorrect

#5

I passed this challenge without order by… why i need it?

-Spoiler-

select
ROUND(watch_duration_in_minutes)
as ‘duration’,
count(watch_duration_in_minutes)
as ‘count’
from watch_history
group by 1

;

3 Likes
#6

As far as I can understand, the “rename process” in order to be completed (by using SELECT… AS…), we have to write the whole command , I mean finish the command by writing the name of the table (FROM watch_history).
So, COUNT (duration) cannnot be identified since the new name (duration) has not been applied yet (the command SELECT … AS is still incomplete).
On the other hand, in whichever command (GROUP BY, ORDER BY) is written afterwards, the new name (duration) can be used.

#7

Same here. Just tried solving the problem and it seems as if the ORDER BY function is not only useless, but it also hampers the resulting query. Your solution works, but with the addition of the ORDER BY function the query turns into null.

#8

It works either way when I copy-paste @karub38’s code with or without ORDER BY 1. (A semicolon needs to be added, though) . Without ORDER BY the ordering appears to be what we want, but are we guaranteed that ordering?

#10

hello All,

i tried all the solutions and at the end … there is no result … here is the code i typed

SELECT ROUND (watch_duration_in_minutes) AS ‘duration’, COUNT (watch_duration_in_minutes) AS ‘count’
FROM watch_history
GROUP BY watch_duration_in_minutes
ORDER BY 2;

where could be the mistake ??

Best regards,

#11

I am actually having some difficulty with this assignment. I have tried multiple lines to get the result correct, but seems to not be working?

Can anyone confirm the correct answer for this section. The round function seems to be incorrect potentially.

#13

I just struggled with his challenged as well but managed to resolve it in the end.

You need to round the watch_duration_in_minutes and rename to duration and then do a count on the watch_duration_in_minutes based on the rounded value.

select round(watch_duration_in_minutes) as ‘duration’,
count(watch_duration_in_minutes) as ‘count’
from watch_history
group by 1
order by 1;

9 Likes
#15

I tried as well after not being able to solve this and it still asks for an ORDER BY to be correct, but i cannot figure out where it should be placed.

#16

SELECT ROUND(watch_duration_in_minutes) AS ‘duration’,
COUNT(*) AS ‘count’
FROM watch_history
GROUP BY duration
ORDER BY duration;

Try this one, It works for me.

3 Likes
#17

Hi everyone, i would like to know why my solution did not work, it is technically the same as the solution given by the plataform i am just missing the aliases, that is just an alias as we were told… if someone may explain me why, please…

My Solution

SELECT ROUND(watch_duration_in_minutes), COUNT(*)
FROM watch_history
GROUP BY watch_duration_in_minutes
ORDER BY watch_duration_in_minutes ASC;

Solution given by the plataform

SELECT
  ROUND(watch_duration_in_minutes,0) as duration,
  COUNT(*) as count
FROM watch_history
GROUP BY duration
ORDER BY duration ASC;
1 Like
#18

One of the Instruction for this exercise is:
The UX Research team wants the result to contain:
duration , which is the watch event duration, rounded to the closest minute
and you completely ignored that instruction in your solution. Hence, it failed.
Technically, your solution is correct and it may work outside the Codecademy but in order to pass the exercise, you must adhere to the instructions given.

Hope this helps,
Cheers

#19

i don’t know how you passed this test as you need to still use ORDER BY there must a glitch with the program as it wouldn’t let me pass to the next test till i ordered them

SELECT ROUND(watch_duration_in_minutes) AS ‘duration’,

COUNT(*) AS ‘count’

FROM watch_history

GROUP BY 1

ORDER BY duration ASC;

#20

so in this example,

group by 1
order by 1

is different from

group by watch_duration_in_minutes
order by watch_duration_in_minutes ASC (this is what I had and it was wrong).

For clarity’s sake, group by 1 is different than naming the actual column, because group by 1 is your modified rounded column and what I did is pulling straight from the database, is that correct?

my query was rounding each result to whole numbers, but still displaying the 1.5 etc as its own 1.0 with its own count, which isn’t what we wanted, and I couldn’t figure out how to get it to stop doing that.

is grouping by column 1, 2 etc the best practice here so you are grouping by your own modified rule set?

2 Likes
#22

does it matter in the group by statement if we use 1 to refer to the column name or if we use the column because my code is changing depending on my usage of 1 or of the column name

1 Like
#23

This one helped, thanks! I noticed that the “Not correct” could just use the quotes on ‘duration’, rather than writing it all the way out again.

#24

Can also use the ‘%M’ rather than the 0 as well.

#25

Hello!

I just found the solution proposed is:

SELECT ROUND(watch_duration_in_minutes) AS ‘duration’,
COUNT(*) AS ‘count’
FROM watch_history
GROUP BY 1
ORDER BY 1 ASC;

From the previous explanation I understood that when using ROUND one should always state the number of decimals places in addition to the column name, like this:

SELECT ROUND(watch_duration_in_minutes, 0) AS ‘duration’,
COUNT(*) AS ‘count’
FROM watch_history
GROUP BY 1
ORDER BY 1 ASC;

So does anyone know if this is or isn’t necessary really?

Thank you!

#26

Just to show you the image of the exercise solution:

image