How to count rows in MySQL with MySQL COUNT function?

Overview

COUNT() is an aggregate function to return the number of rows in a table.

Syntax

To count all rows in a table:

SELECT
	COUNT(*)
FROM
	table_name;

To count all rows that contain non NULL values as the results of an expression:

SELECT
	COUNT(expression)
FROM
	table_name;

To count all rows with unique non NULL values from the expression results:

SELECT
	COUNT(DISTINCT expression)
FROM
	table_name;

To count all rows from the table that match a given condition:

SELECT
	COUNT(*)
FROM
	table_name
WHERE
	condition;

Example:

We have table orders:

id	order_id
1	TK12
2	TK19
3	TP99
4	AD100
5	TU65
6	MA33
7	PP98
8	DE28
9	TH54
10	VD66
11	GT74
12	PO1901
14
15
16
17

Now count all rows in the table orders:

SELECT
	COUNT(*)
FROM
	orders;

The result is 16

Now count all the rows with non NULL order_id values:

SELECT
	COUNT(order_id)
FROM
	orders;

The result is 12.

To count all the rows with id > 7:

SELECT
	COUNT(*)
FROM
	orders
WHERE
	id > 7;

The result is 9

In another example, we have table upcoming_movies:

movie_name	release_year	universe
Joker	2019	DC
The New Mutants	2020	Marvel
Black Widow	2020	Marvel
Birds of Prey	2020	DC
The Eternals	2020	Marvel
Wonder Woman 1984	2020	DC
Doctor Strange and the Multiverse of Madness	2021	Marvel
Thor: Love and Thunder	2021	Marvel
The Batman	2021	DC
The Suicide Squad	2021	DC
Black Panther 2	2022	Marvel
Aquaman 2	2022	DC

Now we count all movies of each universe using GROUP BY:

SELECT
	universe,
	COUNT(*) as total
FROM
	upcoming_movies
GROUP BY
	universe;

The result is:

universe	total
Marvel	6
DC	6

Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL, and many other databases simultaneously using an intuitive and powerful graphical interface.