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
- 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.
The below examples use the following table, which contains some details about fruit:
|banana||yellow||not that tasty|
|kiwi fruit||not tasty at all|
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.