COUNT()

COUNT is like counting heads in a room. COUNT(*) counts every row, while counting a specific column skips the ones left blank.

In this page:

Counting All Rows
Counting Specific Columns
Counting Unique Values
Counting with a Filter
COUNT() with GROUP BY

Syntax

sql

COUNT(*)
COUNT(column_name)
COUNT(DISTINCT column_name)

Counting All Rows

The COUNT() function counts every row that matches your query when called as COUNT(*). It includes rows with NULL values and duplicate entries in the total, since it's counting rows, not evaluating any particular column.

Example: Counting All Rows

sql

CREATE TABLE orders (id INT, note TEXT);
INSERT INTO orders VALUES (1, 'a'), (2, NULL), (3, 'a');
SELECT COUNT(*) AS total_rows FROM orders;

Counting Specific Columns

If you pass a column name to COUNT() instead of a star, it only counts rows where that specific column is not NULL. It ignores any NULL values, which makes it useful for counting how many records actually have a given field filled in.

Example: Counting Specific Columns

sql

CREATE TABLE orders (id INT, note TEXT);
INSERT INTO orders VALUES (1, 'a'), (2, NULL), (3, 'a');
SELECT COUNT(note) AS filled_notes FROM orders;

Counting Unique Values

You can combine COUNT() with the DISTINCT keyword to avoid counting duplicates. This counts only the unique, non-duplicate, non-NULL values in a column, such as counting how many distinct customers placed orders rather than how many orders exist.

Example: Counting Unique Values

sql

CREATE TABLE orders (id INT, customer_id INT);
INSERT INTO orders VALUES (1, 1), (2, 1), (3, 2);
SELECT COUNT(DISTINCT customer_id) AS unique_customers FROM orders;

Counting with a Filter

You can add a WHERE clause to your count query to scope it down. This allows you to count only the rows that match a specific condition, like counting only orders placed this month.

Example: Counting with a Filter

sql

CREATE TABLE orders (id INT, order_month TEXT);
INSERT INTO orders VALUES (1, 'Jan'), (2, 'Feb'), (3, 'Jan');
SELECT COUNT(*) AS jan_orders FROM orders WHERE order_month = 'Jan';

COUNT() with GROUP BY

You can combine COUNT() with a GROUP BY clause to get a count per category instead of one grand total. This allows you to count items for different groups or categories of data in a single query.

Example: COUNT() with GROUP BY

sql

CREATE TABLE orders (id INT, customer_id INT);
INSERT INTO orders VALUES (1, 1), (2, 1), (3, 2);
SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id;

Chapter Quiz — Complete all 7 topics to unlock

0/7 topics done

Complete these topics first:

← Previous Lesson MySQL Aggregate Functions Next Lesson → SUM()