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?
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.
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?
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.
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;
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?
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?
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.
) below!
) to up-vote the contribution!