Skip to content
SQLSimplified
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

Loading playground environment...

Step-by-Step Breakdown

  1. COUNT(*) counts every row in each group (including rows with NULL columns).
  2. GROUP BY director: one row per director.
  3. 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:

Loading playground environment...

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.

Related examples

Core Aggregate Functions: COUNT, SUM, AVG, MIN, MAX
aggregate
A single query demonstrating all five core aggregates over the employees table.
GROUP BY Basics: Aggregating Rows Into Summaries
group by
Collapse individual orders into one summary row per customer using GROUP BY, COUNT, and SUM.
GROUP BY with HAVING
group by
Use HAVING to keep only departments whose average salary exceeds a threshold after aggregation.

Cite this resource

SQLSimplified. "COUNT() Aggregation With GROUP BY Example". Available at: https://sqlsimplified.online/examples/group-by-count