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.

MySQL Show Warnings

MySQL Show Warnings displays the errors, warnings, and note messages that resulted from executing the last statement in the current session. The warnings are generated by DML statements like INSERT and UPDATE and DDL statements like CREATE TABLE and ALTER TABLE.

The warnings are shown only for the last executed statement which used a table. If a statement used a table and generated no message then it will clear the message list and the SHOW WARNINGS diagnostic statement will return nothing. On the other hand, if a statement does not use a table, then it does not effect on the warning message list. 

The Syntax for MySQL Show warnings is as follows:

SHOW WARNINGS [LIMIT [offset,] row_count] SHOW COUNT(*) WARNINGS;

Query-1: Show Warnings Syntax

Here, the first syntax is for showing the actual messages whereas the second syntax is for showing the number of warning counts. 

MySQL Show Warnings

The query below shows how MySQL SHOW WARNINGS can be used to get the warning messages generated by the last executed statement.

mysql> create table users(user_id INT NOT NULL, user_name VARCHAR(5) NOT NULL,salary DECIMAL(10,2) NOT NULL); Query OK, 0 rows affected (0.45 sec) mysql> insert into users values(1,'userA',123.456),(2,'user12',NULL),(3,NULL,100.12); Query OK, 3 rows affected, 4 warnings (0.23 sec) Records: 3  Duplicates: 0  Warnings: 4 mysql> show warnings; +---------+------+------------------------------------------------+ | Level   | Code | Message                                        | +---------+------+------------------------------------------------+ | Note    | 1265 | Data truncated for column 'salary' at row 1    | | Warning | 1265 | Data truncated for column 'user_name' at row 2 | | Warning | 1048 | Column 'salary' cannot be null                 | | Warning | 1048 | Column 'user_name' cannot be null              | +---------+------+------------------------------------------------+ 4 rows in set (0.00 sec) mysql> show count(*) warnings; +-------------------------+ | @@session.warning_count | +-------------------------+ |                       4 | +-------------------------+ 1 row in set (0.00 sec)

Example-1: Show Warnings

Here, we created a users table with user_id as INT, user_name as VARCHAR(5), and salary as DECIMAL(10,2) which are all required. 

Then, we inserted some rows that do not adhere to the table. The first data row has the salary in 3 significant digits, the second row has the username of length 5 and does not have salary data, and the last row does not have a username. On the execution of the statement, the server returned 4 warnings count. 

The warning message generated by the query can be viewed using the MySQL show warnings as shown in the above example.

Show Warnings mysqlimport 

We see how we can show warnings for the queries but what about when we import data using mysqlimport command? The answer is we mysqlimport and show warnings do not work together. So, to show warnings while importing data we have to use other methods if we want to show the warning messages. 

We can achieve this using the MySQL LOAD DATA INFILE command followed by the show warning command. 

The data to be imported is kept in import.txt file as follows:

1,userA,123.456 2,user12,NULL 3,NULL,100.12

Import.txt

Now, the data can be imported with show warnings as follows:

LOAD DATA INFILE 'D:/import.txt' INTO TABLE users FIELDS TERMINATED BY ','; show warnings; Query OK, 3 rows affected, 3 warnings (0.14 sec) Records: 3  Deleted: 0  Skipped: 0  Warnings: 3 +---------+------+---------------------------------------------------------------+ | Level   | Code | Message                                                       | +---------+------+---------------------------------------------------------------+ | Note    | 1265 | Data truncated for column 'salary' at row 1                   | | Warning | 1265 | Data truncated for column 'user_name' at row 2                | | Warning | 1366 | Incorrect decimal value: 'NULL' for column 'salary' at row 2 | +---------+------+---------------------------------------------------------------+ 3 rows in set (0.00 sec)

Example-2: Show Warnings on data import