How to Use the MySQL/MariaDB COUNT Function, With Examples

This quick article will demonstrate how to use the MySQL/MariaDB COUNT function to count the number of records returned by a query.

MySQL/MariaDB COUNT Function Syntax

COUNT(query)

Note that:

• query can be an SQL query or a string representing a column name
• Null values found by the query will not be counted
• The COUNT function will return the number of matching records for a query and should be combined with the SELECT statement to output the result.

Example Data

The below examples use the following table, which contains some details about fruit:

fruit table:

Count All Rows in A Table

To COUNT all rows in a table, simply pass a wildcard (*) to the COUNT function:

SELECT COUNT (*) FROM fruit;

This would output:

The SELECT query will output the data in a single column containing the count.

Naming COUNTed Columns using AS

That column name is a bit ugly – it can be renamed using an AS statement:

SELECT COUNT (*) AS totalCount FROM fruit;

Which will return a tidier looking:

Count Only Values in a Column

The values in a single column can be counted – null values will not contribute to the count. The below query will count only rows where the color field is set:

SELECT COUNT ('color') AS colorCount FROM fruit;

As one of the records is missing its color, it will not be counted:

Grouping and Counting

COUNT can be useful when wanting to summarise the data in a table. For example, to count how many fruits of each color are in the example table:

SELECT color, COUNT(*) FROM fruit GROUP BY color;

Find out more about the COUNT function in the official MySQL documentation here.

The DISTINCT operator can be used to find the unique values in a MySQL column.