group by

COUNT() Aggregation With GROUP BY

Count how many movies each director has made using COUNT() grouped by director.

Overview

COUNT() tallies rows. Combined with GROUP BY, it becomes a frequency counter — exactly what you need to answer "how many X per Y". Here we count movies per director in the movies dataset.

The Query

Movies (Movies/Actors/Ratings)

Loading database engine...

Step-by-Step Breakdown

COUNT(*) counts every row in each group (including rows with NULL columns).
GROUP BY director — one row per director.
ORDER BY movie_count DESC surfaces the most prolific directors first.

COUNT(*) vs COUNT(column)

COUNT(*) counts rows; COUNT(release_year) counts only rows where that column is non-null. They differ when the column can be NULL.

Variations

Count books per author in the library dataset:

Library (Books/Authors/Publishers)

Loading database engine...

Common Mistakes

Using COUNT(column) expecting row counts. If the column has NULLs you undercount; prefer COUNT(*) for "how many rows".
Forgetting GROUP BY. Without it, COUNT(*) returns a single grand total.
Selecting non-grouped columns. SELECT title, COUNT(*) without grouping title is invalid.