From Subqueries to Having and Pandas | Detailed 🏆
My SQL\n\n## Using Subquery:\n\nsql []\nSELECT class\nFROM (\n    SELECT class, COUNT(student) AS num\n    FROM courses\n    GROUP BY class\n    ) AS temp_table
# My SQL\n\n## Using Subquery:\n\n```sql []\nSELECT class\nFROM (\n    SELECT class, COUNT(student) AS num\n    FROM courses\n    GROUP BY class\n    ) AS temp_table\nWHERE num >= 5\n```\n\n## Using Having with Group By\n```sql []\n# Write your MySQL query statement below\nSELECT class\nFROM courses\nGROUP BY class\nHAVING COUNT(student) >= 5\n```\n\n# Pandas\n\nWe use the function `size()` to count the number of occurrences for each unique class, which gives us the count of students in each class. We use `reset_index(name=\'count\')` to name this column as `count`.\n\n## Using Count:\n`count()` simply counts the rows after doing group by. So, after grouping by class, the count operator will count how many rows are there.\n\n``` python3 []\nimport pandas as pd\n\ndef find_classes(courses: pd.DataFrame) -> pd.DataFrame:\n  df = courses.groupby(\'class\').count().reset_index()\n  df = df[df[\'student\']>=5]\n  return df[[\'class\']]\n```\n\n## Using Size:\n\nUsing `size()` creates a new column with header 0. So, we need to name that column by providing `name=\'count\'` inside of `reset_index()`.\n``` python3 []\nimport pandas as pd\n\ndef find_classes(courses: pd.DataFrame) -> pd.DataFrame:\n  df = courses.groupby(\'class\').size().reset_index(name=\'count\')\n  df = df[df[\'count\'] >= 5]\n  return df[[\'class\']]\n```
['MySQL', 'Pandas']