Grouping is where SQL becomes really powerful. Any question that says "for each actor…", "per genre…", "which director…" is a GROUP BY question. Nitish stresses it because a lot of interview questions are built on it.
The idea: split, apply, combine
Question: who are the top 5 actors by number of movies? By hand you would:
- Split the table into one small table per actor (all of Akshay's rows, all of Aamir's rows…).
- Apply an aggregate to each small table: count its rows, i.e. how many movies.
- Combine the results into one row per actor, then sort descending and keep 5.
That's exactly what GROUP BY does: it creates groups on the basis of a column, and aggregate functions then run once per group.
Your first GROUP BY
-- movies per actor
SELECT actor, COUNT(*) AS num_movies
FROM movies
GROUP BY actor;Now sort and keep the top 5. That's the full answer:
SELECT actor, COUNT(*) AS num_movies
FROM movies
GROUP BY actor
ORDER BY num_movies DESC
LIMIT 5;In the class's full dataset the answer was Mithun Chakraborty, then Akshay Kumar, Amitabh Bachchan, Ajay Devgn and Sanjay Dutt.
After grouping, every column in SELECT must be either a grouping column (here actor) or inside an aggregate (COUNT, SUM, AVG, MIN, MAX). SELECT actor, title … GROUP BY actor makes no sense: one actor has many titles, so which one should be shown? (Strict MySQL rejects it with ONLY_FULL_GROUP_BY.)
The questions from class
A rich new producer wants to invest in Bollywood: which genre earns the most total profit?
SELECT genre, SUM(worldwide - budget) AS total_profit
FROM movies
GROUP BY genre
ORDER BY total_profit DESC
LIMIT 5;In class: Drama first, then Action, then Masala. Animation was at the bottom; it rarely does well in India.
Whose movies make the most profit on average? Use AVG, not SUM, so directors with many films don't win just by volume.
SELECT director, AVG(worldwide - budget) AS avg_profit
FROM movies
GROUP BY director
ORDER BY avg_profit DESC
LIMIT 5;Class result: S. S. Rajamouli, Nitesh Tiwari, Rajkumar Hirani, Ali Abbas Zafar…
SELECT actor, SUM(worldwide - budget) AS total_profit
FROM movies
GROUP BY actor
ORDER BY total_profit DESC
LIMIT 10;Nitish's observation: Prabhas jumps into the top 10 with very few films (Baahubali). Older stars like Amitabh Bachchan rank lower because the numbers aren't adjusted for inflation.
A student asked how to get the name of the highest-budget movie. Nitish showed a roundabout trick: group by title (so every movie is its own group), then sort.
SELECT title, budget
FROM movies
GROUP BY title
ORDER BY budget DESC
LIMIT 5;It works, but the GROUP BY adds nothing here; plain ORDER BY budget DESC LIMIT 5 gives the same answer. The proper "name of the max" tool is a subquery (Chapter 12).
Grouping by several columns
Which actor + director combination has made the most profit? Now each group is one combination of the two columns:
SELECT actor, director, SUM(worldwide - budget) AS total_profit
FROM movies
GROUP BY actor, director
ORDER BY total_profit DESC
LIMIT 5;Class result: Salman Khan + Ali Abbas Zafar (Sultan, Tiger Zinda Hai), Salman + Kabir Khan, Aamir + Rajkumar Hirani (3 Idiots, PK), Prabhas + Rajamouli, Aamir + Nitesh Tiwari. Swap director for genre to find which actor earns most in which genre:
SELECT actor, genre, SUM(worldwide - budget) AS total_profit
FROM movies
GROUP BY actor, genre
ORDER BY total_profit DESC
LIMIT 5;HAVING: filtering groups
WHERE filters rows before grouping. To filter on an aggregate (after grouping) you need HAVING. Question: which actors' movies open, on average, on more than 3,500 screens? (More opening screens usually means a bigger star.) There's no "top N" here, so LIMIT won't help; it's a condition on the group's average.
SELECT actor, AVG(screens) AS avg_screens
FROM movies
GROUP BY actor
HAVING avg_screens > 3500
ORDER BY avg_screens DESC;Writing each clause on its own line, as above, is just for readability. Many people prefer it.
| WHERE | HAVING | |
|---|---|---|
| Filters | individual rows | whole groups |
| Runs | before GROUP BY | after GROUP BY |
| Can use aggregates? | No | Yes (HAVING COUNT(*) > 2) |
Both together: among movies released after 2012 (WHERE), which genres have more than 2 movies (HAVING)?
SELECT genre, COUNT(*) AS n
FROM movies
WHERE release_date > '2012-12-31'
GROUP BY genre
HAVING COUNT(*) > 2;No. CHECK is a constraint applied when creating a table (Chapter 7); it's not a query clause. Filtering groups is HAVING's job.
Clause order: memorise this
SELECT → FROM → JOIN → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT
Writing them in any other order is a syntax error (e.g. WHERE after ORDER BY). The logical execution order is different (FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT; see Chapter 8).
✍️ Writing order: how you type it
⚙️ Execution order: how it runs
The orange number on each written clause is its execution step. Hover (or tap) a clause to see where it sits in the other list. SELECT is written first but runs 6th, which is why WHERE can't use a SELECT alias but ORDER BY can.
SQL lab
Exercise ideas
- Survival rate by
sex, then bysex, pclasstogether. - Average fare per port of embarkation, highest first.
- Directors who made more than one movie in the dataset (HAVING).
- For each genre: number of movies, total budget, and average runtime in hours.
Conceptual questions
These check whether you understand why, not just what. Several go one step beyond the video.
Why can't you write WHERE COUNT(*) > 2?
Group-level conditions belong in HAVING.
When both work, why is filtering with WHERE usually better than HAVING?
E.g. WHERE genre = 'Drama' beats HAVING genre = 'Drama'.
Top directors by average profit can be misleading. Why, and what's a fix?
Small groups give noisy averages: a classic data-science pitfall.
SELECT pclass, AVG(survived) FROM train GROUP BY pclass. What does the average of a 0/1 column mean?
The mean of a binary indicator is a proportion, a handy trick in analytics.
How do COUNT(*) and COUNT(age) differ inside a group?
All aggregates except COUNT(*) ignore NULLs.