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;
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.
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.
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?
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;
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;
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;
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.
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’,
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?
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