Lecture 16 – Counting Rows with COUNT

This lecture introduces the COUNT() aggregate function.

Welcome to the world of aggregate functions. An aggregate function performs a calculation on a set of rows and returns a single, summary value. They are incredibly powerful for data analysis.

The first and most common aggregate function we will learn is COUNT().

What Does `COUNT()` Do?

The COUNT() function simply counts the number of rows that match a certain condition. It answers questions like “How many contacts are in my address book?” or “How many sales did we make today?”

Counting All Rows

To count every single row in a table, you use COUNT(*). The asterisk (*) means “all rows”.

Let’s find out how many contacts are in our `Contacts` table:

SELECT COUNT(*) FROM Contacts;

The result of this query is not a table of data, but a single number representing the total number of rows in the `Contacts` table.

Using an Alias

You might notice the column header in the result is just `COUNT(*)`. That’s not very descriptive. You can give the result column a more readable name using the AS keyword to create an alias.

SELECT COUNT(*) AS TotalContacts FROM Contacts;

Now, the result will be a single number in a column named `TotalContacts`.

Counting Rows that Match a Condition

COUNT() becomes even more useful when you combine it with a WHERE clause. This allows you to count a subset of your data.

Let’s use the `Sales` table from our previous lecture. How many times was an ‘Apple’ sold? We can find out by counting the rows where the `ItemName` is ‘Apple’.

-- First, let's recreate our Sales table for this example
CREATE TABLE Sales (
    SaleID INTEGER PRIMARY KEY,
    ItemName TEXT,
    Price REAL
);

INSERT INTO Sales (ItemName, Price) VALUES ('Apple', 0.50);
INSERT INTO Sales (ItemName, Price) VALUES ('Banana', 0.30);
INSERT INTO Sales (ItemName, Price) VALUES ('Apple', 0.50);
INSERT INTO Sales (ItemName, Price) VALUES ('Orange', 0.60);

-- Now, let's count the apples
SELECT COUNT(*) AS NumberOfApplesSold FROM Sales WHERE ItemName = 'Apple';

This query will return a single value: 2.

`COUNT()` with `DISTINCT`

You can also use COUNT() with the DISTINCT keyword to count the number of unique values in a column. For example, how many *different kinds* of items have we sold?

SELECT COUNT(DISTINCT ItemName) AS UniqueItemsSold FROM Sales;

This will return the number 3, because we have sold three unique types of items: Apple, Banana, and Orange.

In the Next Lecture…

We will explore two more essential aggregate functions: SUM() and AVG(), which allow us to calculate the total and the average of the values in a numeric column.

Lecture 15: Finding Unique Values with DISTINCT