COUNT()
In this page:
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
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
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
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
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
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: