Aggregating Data with COUNT()

The Big Picture: Aggregate Functions

Summary over Detail

In data analysis, we often need to look at the big picture. Instead of listing every individual movie, we might want to know how many movies exist in total.

Aggregate Functions perform calculations across a set of values and return a single, summarized result.

Welcome to Module 4. Until now, we've focused on retrieving specific rows and columns. But what if you need a summary? This is where Aggregate Functions come in. They take a large set of data and boil it down to a single, meaningful number.

Summarizing Data with Aggregation

Summarizing vs. Listing

So far, we've used SELECT to list individual rows. But what if you need a big-picture summary? Aggregate Functions perform calculations on a set of values and return a single value.

In previous modules, you learned how to retrieve specific rows from a database. However, data analysis often requires summarizing information rather than just listing it. Instead of seeing every film, you might just want to know how many films are in your collection. This is where Aggregate Functions come in.

The Power of COUNT()

Understanding COUNT()

The COUNT() function tells you how many rows match your search criteria. There are two primary ways to use it:

There are two ways to use the COUNT function. COUNT star counts every single row in the table, regardless of the data inside. However, COUNT with a column name only counts rows where that specific column has a value, ignoring any NULL or empty entries.

Visualizing the Movie Schema

The Movie Database Structure

To write effective queries, you must understand the Schema. Our database consists of two tables: Movie and Rating.

Before we start counting, let's look at our tools. Here is our database schema. Notice the Primary Keys in each table. The RatingCode in the Movie table acts as a Foreign Key, connecting every movie to its specific rating description.

Our Movie Database Schema

Database Relationships

We will use the Movie and Rating tables. Notice how RatingCode links the two.

Before we write queries, let's look at our schema. The Movie table contains the Title, Genre, and Year. The RatingCode in the Movie table connects to the Code in the Rating table, allowing us to see the full description of a film's rating.

The COUNT(*) Syntax

Counting Every Row

The basic syntax to find the total number of records is:

SELECT COUNT(*) FROM Movie;

To find out how many movies are in our collection, we use the SELECT COUNT statement. The asterisk inside the parentheses is a wildcard—it tells the database to count every single row in the table, resulting in one total number.

Filtering Before Counting

Using WHERE with COUNT()

You can refine your count by adding a WHERE clause. This allows you to count only the items that meet your criteria.

Try changing the year to see how the count updates.

You don't always want to count everything. By adding a WHERE clause, you can filter the data first. Try clicking on different years to see how the count changes dynamically. Great! Notice how the SQL engine first finds the rows matching that year, and then performs the COUNT function on that subset.

Syntax: Finding the Total Count

Writing the Query

To find the total number of movies, follow this structure:

SELECT COUNT(*)
FROM Movie;

To find out the total number of movies in the Movie table, you would write a simple query. Start with the SELECT keyword followed by COUNT star. Then use the FROM keyword followed by the table name, Movie.

Construct the Query

Drag the SQL clauses into the correct order to count all records in the Movie table.

Let's practice the structure. Drag these query fragments into the correct order to count all the movies in our database. Correct! The database first looks at the table defined in the FROM clause, then performs the COUNT calculation defined in the SELECT clause.

Common Pitfalls: COUNT(*) vs COUNT(column)

Handling Missing Data

Beginners often wonder if they should use a column name or an asterisk. There is a key difference:

Look closely at this data. One movie is missing its Genre. If we use COUNT asterisk, we get a total of five rows. But if we count the Genre column specifically, we only get four, because SQL ignores the empty cell.

Filtering Before Counting

COUNT() with WHERE

You can combine COUNT() with a WHERE clause to count specific subsets of data.

SELECT COUNT(*)
FROM Movie
WHERE Year = 2022;

What if you only want to count specific movies? Imagine we have a list of movies from different years. The WHERE clause filters the list first, keeping only the rows that match your criteria. Finally, COUNT looks at that filtered list and returns the total.

Write Your First Aggregate Query

Write a query to find the total number of movies in the Movie table that belong to the 'Sci-Fi' genre.

It's your turn. Write a query to find how many 'Sci-Fi' movies are in the database. Remember to use single quotes for text values.

Interactive Exercise: Count the Comedies

Your Turn!

Write a query to find out how many 'Comedy' movies are in the Movie table. Remember to use UPPERCASE for SQL keywords.

It's time to put your skills to the test. Write a complete SQL query to count all movies where the Genre is 'Comedy'. Type your query in the box and hit submit.

Final Challenge: The Year 2020

Final Assessment

A producer wants to know exactly how many movies were released in the Year 2020. Write the query to find this information.

For your final challenge, write a query to count all movies released in the year 2020. Good luck!