hey Nisha — I updated the article after all.
Thanks for the note! 😉

Yeah, spot-on – and indeed, Nisha has already found this one.

Regardless: Great catch and thanks for the comment!

PS. And keep going with the Python and bash articles. : )

Thank you for the awesome tutorial. I enjoyed working my through it. I will also continue with the bash and the Python tutorials soon 🙂

I just have one question to SQL now:
For the first example you have here in this last article (where we should only print the tailnumber of the plane) i found a solution which seems to be working, without using any subqueries:

SELECT
tailnum
FROM flight_delays
GROUP BY tailnum
ORDER BY AVG(depdelay) DESC
LIMIT 10;

I just added the avg when I was about to order the list. The results turned out to be the same as your results. It looks correct to me. Is this something which one could do, or is it maybe less ‘proper’ to use it this way?

hi William! Glad to hear! 😉 And thanks for the feedback! Tomi

hi Xiao,
hmm. tricky – TBH, I’m not really familiar with MS SQL, sorry… but it’s like 99.9% sure that issue comes from the difference between MS SQL and PostgreSQL.
Tomi

Hi Tomi,

thanks for your reply.

I just tested case statement + Group by , seems the combination does not work, getting error of “Invalid column name”. I am just curious about when you run your query, did you meet any error or simply got the output?
PS. I used MS SQL.

Thanks,
Xiao

hi Xiao,

thanks for the question! To be honest, I don’t know the exact answer and I couldn’t find anything online that provides a definite answer for your question… Although, my best guess is that “CASE” executed – independently from the SELECT statement – before the GROUP BY statement.
But if anyone finds a better answer, don’t hesitate to comment!

Tomi

Very good posts and website that I have learned lots of knowledge of Data Analytics, thanks.

Just wonder how this following code “Group by segment” works

SELECT COUNT(*),
CASE WHEN depdelay 0 THEN ‘late’
ELSE ‘ontime’
END as segment
FROM flight_delays
GROUP BY segment
LIMIT 10;

because Group by comes before Select, when SQL executes group by segment, SQL did not recognize “segment” which comes from Select clause.

I don’t know if my understanding is correct, but you still got results.