How to count the number of rows in SQL?

Syntax

We use the COUNT() function to count the number of data rows in a table that matched specified criteria.

Here is the generic syntax:

COUNT(*)
COUNT( [ALL|DISTINCT] expression )

The COUNT() function is an ANSI standard function and it’s universally used across multiple relational database systems, so the syntax above is expected to work for most databases including MySQL, PostgreSQL, SLite, SQL Server, except Oracle is a bit different:

COUNT ({*|[DISTINCT] expression}) OVER (window_clause)

When you use COUNT(*), it counts all rows including rows containing NULL values, but when you use COUNT(column), it only counts the non-NULL values in that column.

Example

We want to count the number of records from the table salaries in MySQL:

SELECT COUNT(*) AS total_row FROM salaries;

The result will be like:

total_row
1914316

But count by the columns email_address, which has 10000 rows with a NULL value:

SELECT COUNT(email_address) AS total_email FROM salaries;

The results will be:

total_email
1904316

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.

TablePlus