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

MySQL Count

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:

name color tastiness
apple green very tasty
lemon yellow the tastiest
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:

COUNT(*)
4

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:

totalCount
4

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:

colorCount
3

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;
color COUNT(*)
green 2
yellow 2

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.

SHARE:
nv-author-image

Brad Morton

I'm Brad, and I'm nearing 20 years of experience with Linux. I've worked in just about every IT role there is before taking the leap into software development. Currently, I'm building desktop and web-based solutions with NodeJS and PHP hosted on Linux infrastructure. Visit my blog or find me on Twitter to see what I'm up to.

Leave a Reply

Your email address will not be published. Required fields are marked *