LeetCode 596: Classes More Than 5 Students [SQL]

[@YASHKUMARJAIN]

Great work Frederik. Waiting for more leetcode database content.

[@ayeoh47]

this is a great problems, i had fun solving it. selecting class with more than 5 students, making a temp table that identifies the student count of each class, running filter on this temp table to derive class with >= 5 students.

[@jaimegcia]

Thank you so much for this man, It really helps. As someone currently looking for a related job, this helps feed my confidence.

[@frederikmuller]

hang in there and good luck with your interviews!

[@penumono]

This worked for me SELECT class FROM Courses GROUP BY class HAVING COUNT(class) > 4;

[@yuxuan2393]

thanks a lot！

[@daniellesong3686]

Hi Frederik, thanks a lot for the tutorial, I'm new to SQL and would like to learn how to count by classes, e.g. how many "Math" appears in the column, and return the class name if the count number is >=5. Looking forward to your feedback. Thanks.

[@frederikmuller]

Hi Danielle, I'm glad to see you're trying to learn SQL. If you would like to count by classes you could just SELECT class, COUNT(*) FROM table GROUP BY class If you would like to get the count "Math" values only, you could use the following construct: SELECT SUM(CASE WHEN class = "Math" THEN 1 ELSE 0 END) FROM table This will count each occurrence of "Math" as a 1 and everything else as a 0. Calculating the sum of this column will give you the amount of "Math" values in that column. I hope that makes sense!

[@daniellesong3686]

@@frederikmuller Hi Frederik, thanks for the prompt reply. It's very helpful. I tried COUNT BY class, the output is as expected. But my mistake is not using DISTINCT student. That makes the submission is not accepted. Note in each problem is very important clue for coding....

[@frederikmuller]

@@daniellesong3686 I'm glad you found the issue. Right, this problem asks for the number of students per class and not just number of rows per class.

[@abhinavkumar6584]

can't wee also use class in count ?

[@frederikmuller]

if there are no duplicate entries, yes. the note in the question mentions the possibility of having duplicates in the table, so using COUNT(DISTINCT student) is best.