MySQL: How to count the number of rows by a condition

Introduction

Understanding how to count rows in MySQL subject to certain criteria is a fundamental skill useful in many scenarios. Counting rows with a condition can help you analyze your data effectively – whether it’s for simple tasks like tallying the number of users in a system, or more complex operations like generating reports or informing business logic. In this tutorial, we will discuss various ways to count rows by a condition using the MySQL database management system.

Basic Count Syntax

To start with, the COUNT() function in MySQL is used to return the number of rows that match a specified condition. It’s one of the Aggregate Functions that MySQL supports, allowing users to perform a calculation on a set of values and return a single value.

SELECT COUNT(*) FROM table_name WHERE condition;

Using * in the COUNT() function counts all rows, regardless of NULL values. If you need to count only rows where a certain column is not NULL, you could use the column name instead of *.

SELECT COUNT(column_name) FROM table_name WHERE condition;

Counting Rows with Simple Conditions

Let’s look at an example using a fictional users table:

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(255) NOT NULL,
  active BOOLEAN DEFAULT TRUE
);

To count all active users:

SELECT COUNT(*) FROM users WHERE active = TRUE;

If we want to count only active users with usernames starting with ‘A’, we would use the LIKE operator:

SELECT COUNT(*) FROM users WHERE active = TRUE AND username LIKE 'A%';

This will count all active users whose usernames start with the letter ‘A’.

Counting Rows with Multiple Conditions

SQL allows for the use of multiple conditions using AND and OR operators. For example:

SELECT COUNT(*) FROM users WHERE (active = TRUE OR active IS NULL) AND (username LIKE 'A%' OR username LIKE 'B%');

This query would return the number of users who are either active or have a NULL ‘active’ value and whose username starts with ‘A’ or ‘B’.

Grouping Results

Counting rows often goes hand in hand with the GROUP BY clause, which is used to group rows that have the same values in specified columns into summary rows:

SELECT column_name, COUNT(*) FROM table_name WHERE condition GROUP BY column_name;

For example, if you wanted to know how many users have the same username, erroneous duplicates, you would use:

SELECT username, COUNT(*)
FROM users
GROUP BY username
HAVING COUNT(*) > 1;

This query groups users based on their username, then counts how many times each username appears and only shows those that appear more than once.

Advanced Conditions with JOINs

Complex conditions might require a JOIN operation, which combines rows from two or more tables, based on a related column between them. Here’s an example of counting rows after joining tables:

SELECT COUNT(DISTINCT u.id)
FROM users AS u
JOIN orders AS o ON u.id = o.user_id
WHERE o.status = 'completed';

This query would count the number of users who have placed an order with a ‘completed’ status, accounting for distinct users.

Utilizing Subqueries

A subquery or inner query or nested query is a query within another SQL query, which can be used to perform operations that require a temporary table:

SELECT COUNT(*) FROM (
  SELECT username FROM users WHERE active = TRUE
) AS active_users;

Here, we count active users from a derived table that contains only usernames of active users.

Conclusion

Mastering the COUNT() function along with various conditions and clauses is essential for data manipulation in MySQL. In this tutorial, we looked over how to perform this task, showing how versatile and powerful SQL queries can be when retrieving data. As you work more with databases, you’ll find countless ways to adapt these approaches to suit your data analytics and reporting needs.