Count Rows in MySQL

The MySQL COUNT() function allows us to find out how many records are in a table. We can also count the number of records that meet certain criteria. In this article, we will learn how to get the number of rows in a table and count records of a query using the MySQL COUNT() function.

Database tables and query results vary in size and number of records. Some tables, like transaction tables, may have millions of records or more. Finding out and monitoring the growth of tables through record count is a key activity done during development and operations.

MySQL COUNT() Function

The function to get the count of records or row count in MySQL is the MySQL COUNT() function. The function is an aggregate function and works on the entire set of records to find a single summarized result.

The count function can be used to accomplish the following:

1. Get the number of rows in a table.
2. Get the number of rows returned by a query or an aliased table
3. Get the number of non-null values taken by a column of a table
4. Get the number of distinct or unique values taken by a column of a table.

Syntax of MySQL COUNT()

MySQL COUNT function has the following syntaxes:

1. COUNT(expression)
3. COUNT(DISTINCT expression)

The expression argument can take several different forms as below:

1. If the expression is an asterisk “*” (star), the count function counts rows in a table or query aliased as a table. The syntax is COUNT(*).
2. If the expression is a column name, the count function counts the total number of non-null values taken by the column. It will count each record, including duplicates.

For example, COUNT(product_id) will return the count of all product_id values, where product_id is a column.

      3. If the expression is of the form “DISTINCT column_name(s)” the count function returns the count of unique combinations of column(s).

For example, COUNT(DISTINCT product_sku, product_name) will count the number of unique combinations of product_sku and product_name columns.

These options make COUNT() function a key function to find not only the number of records but also find answers to questions like:

• Count the number of times a value occurs in a column.
• Show only those categories that occur x or more times in a query/table.
• Sort results according to the number of values taken by a column

The result of the MySQL COUNT function is BIGINT.  The COUNT function returns a 0 if there are no rows returned. COUNT never returns NULL.

The COUNT function is an aggregate function and can be used along with the GROUP BY clause. When used with GROUP BY, the count is evaluated on each combination of attributes in the GROUP BY. We will see an example in the next section.

Conditional Count with MySQL COUNT and IF

The MySQL COUNT syntax allows an IF condition inside the COUNT function. If the result of the IF condition is FALSE, the row is excluded from the count. The syntax is as follows:

1. COUNT(IF(expr))

Apart from IF, other conditional clauses like CASE or IF-ELSE could also be used within the COUNT function for requirements with more complex conditional logic.

As an example, let’s say we have a Customers table. This table has customer information including their demographical information. The customer table has a column “geo” indicating which geography the customer belongs to (North America, Europe, Africa, etc.).

To find out the number of customers in each geography we can do it SQL in at least two ways.

The first way is to use a GROUP BY clause like below:

1. SELECT geo, COUNT(*) as count_customers FROM customers GROUP BY geo;

Output:

The second way is to utilize the conditional count as follows:

1. SELECT ‘Customer count’ Measure, COUNT(IF(geo=’America’,1,NULL)) count_customers_america,
3. COUNT(IF(geo=’Europe’,1,NULL)) count_customers_european,
5. COUNT(IF(geo=’Africa’,1,NULL)) count_customers_european
7. FROM customers;

Output:

As we can see, the first approach results in a normalized output with 3 records, whereas the second approach is a columnar approach with a denormalized and pivoted result.

More Examples for MySQL COUNT()

For these examples, let us create a simple table with the following query:

1. CREATE TABLE T3 AS
3. SELECT 1 AS COL1, 'A' AS COL2, 'Y' AS COL3
5. UNION
7. SELECT 2, 'B', 'Y'
9. UNION
11. SELECT 3, 'C','Y'
13. UNION
15. SELECT 4, 'D', 'N'
17. UNION
19. SELECT 5, NULL, 'Y'
21. UNION
23. SELECT 6, NULL, 'N'
25. UNION
27. SELECT 7, 'G', 'Y';

We first run a SELECT statement to look at the records.

Creating table T3 for illustration.

Example 1. Let us run the following query to count all rows in the table using the expression COUNT(*):

Count all records in a table in MySQL.

Example 2. To count all non-null values in a specific column, we can run the following query:

1. SELECT
3. count(col1) count_col1,
5. count(col2) count_col2,
7. count(col3) count_col3
9. FROM T3;

Count all non-null records in a table in MySQL.

Notice that the two NULL values in col2 were not counted.

Example 3. To count how many Y and N values are in Column 3, we can use the COUNT function with an IF statement. The conditional count query is as follows:

1. SELECT
3. COUNT(IF(col3='Y',1,NULL)) AS COUNT_Y,
5. COUNT(IF(col3='N',1, NULL)) AS COUNT_N
7. FROM T3;

Conditional count of records in a table in MySQL.

Example 4. We can also use the GROUP BY clause with the COUNT function to count the number of Y and N values using the following query:

1. SELECT
3. col3,
5. COUNT(*)
7. FROM T3
9. GROUP BY col3;

Count of records in a table with the GROUP BY clause in MySQL.

Example 5. We can also run a COUNT on a filtered query using the WHERE clause.

1. SELECT COUNT(*)
3. FROM T3
5. WHERE col3='Y';

Count of records in a filtered query with the WHERE clause in MySQL.