HOWTO · MySQL

MySQL COUNT IF Method

Learn how COUNT() and IF() combine to count matching rows and distinct values in MySQL.

On this page

To count rows that meet a condition in MySQL, combine COUNT() with IF(): return a non-NULL value for a match and NULL for every other row. COUNT() counts only non-NULL values, so this pattern counts the matches. “COUNT IF” is a common name for this query pattern, not a separate MySQL function or method.

The same idea can count distinct values among matching rows, and SUM(CASE) is another way to calculate conditional totals. The examples below show how to choose and adapt each form.

How Conditional Counting Works in MySQL

The general expression is COUNT(IF(condition, 1, NULL)). IF() evaluates the condition for each row. It returns 1 when the condition is true and NULL otherwise. The aggregate COUNT() ignores those NULL results and counts the ones, producing the number of matching rows.

For example, COUNT(IF(grade > 80, 1, NULL)) counts rows whose grade is above 80. The condition can use a column comparison or combine predicates with AND or OR. If the condition evaluates to NULL, IF() takes its false branch, so that row contributes no count.

This differs from COUNT(*), which counts every row, and COUNT(column), which counts rows where that column is non-NULL. With conditional counting, the expression inside COUNT() decides which rows contribute. Use a descriptive alias after AS so the result column is easy to identify.

Count Rows That Match a Condition

Suppose a students table has one row per student and a grade column. To count students with a grade above 80, use SELECT COUNT(IF(grade > 80, 1, NULL)) AS high_scorers FROM students;. To add another requirement, combine it with AND, as in COUNT(IF(grade > 80 AND grade_level = 10, 1, NULL)). Both predicates must be true for a row to contribute.

Here is a small, illustrative dataset:

student grade grade level
Alex 85 10
Blair 75 10
Casey 92 11
Drew 88 10

The first query counts three grades above 80. The combined condition counts two students who are above 80 and in grade level 10. These are hand-worked results for the displayed rows; the SQL is not executed here because this example has no MySQL server fixture.

Run this Python illustration of the same row-counting logic. It uses the sample values above; it does not execute a MySQL query.

students = [
    {"grade": 85, "grade_level": 10},
    {"grade": 75, "grade_level": 10},
    {"grade": 92, "grade_level": 11},
    {"grade": 88, "grade_level": 10},
]
high_scorers = sum(student["grade"] > 80 for student in students)
high_scorers_in_grade_10 = sum(
    student["grade"] > 80 and student["grade_level"] == 10
    for student in students
)
print(f"high_scorers: {high_scorers}")
print(f"high_scorers_in_grade_10: {high_scorers_in_grade_10}")
high_scorers: 3
high_scorers_in_grade_10: 2

Count Distinct Values That Match a Condition

To count unique IDs for rows that match a condition, put the value to deduplicate in the true branch: COUNT(DISTINCT IF(condition, expression, NULL)). For each matching row, IF() returns expression; for other rows it returns NULL. COUNT(DISTINCT ...) ignores NULL and counts each distinct non-NULL result once. Duplicate IDs therefore contribute only once, and a matching row whose expression is NULL contributes nothing.

For example, given a student_details table with stu_id and stu_firstName, SELECT COUNT(DISTINCT IF(stu_firstName LIKE '%reet', stu_id, NULL)) AS count_student_ids FROM student_details; counts distinct student IDs whose first name ends in reet. LIKE uses % to match any sequence of characters before reet. The alias count_student_ids names the single result column. An outer SELECT DISTINCT is unnecessary because the aggregate already returns one result row.

Here is a small sample for that query:

stu_id stu_firstName
101 Preet
102 Harpreet
101 Simreet
103 Maya
104 Suneet

The query returns 2: IDs 101 and 102 match the name condition. ID 101 appears twice and is counted once, while ID 104 (Suneet) does not end in reet. The selectable result is:

count_student_ids
2

Use this form when the question is about unique qualifying values, such as distinct customer IDs among orders that meet a condition. Use ordinary COUNT(IF(...)) when each qualifying row should count separately, even if some other value repeats.

Use SUM(CASE) for Conditional Counts

SUM(CASE) provides an alternative when you want to return an explicit numeric contribution for each row. For the student example, SUM(CASE WHEN grade > 80 THEN 1 ELSE 0 END) adds one for each high grade and zero for every other row. With the displayed four rows, that total is 3. For two conditions, put both in the WHEN predicate, for example SUM(CASE WHEN grade > 80 AND grade_level = 10 THEN 1 ELSE 0 END); the total for the sample is 2.

Choose COUNT(IF(...)) when the NULL-based rule is concise and clear. Choose SUM(CASE) when zero-versus-one contributions make the calculation easier to extend or read alongside other conditional totals. Both patterns express conditional aggregation; neither is a standalone “COUNT IF” function. Keep the condition, the counted or summed value, and the output alias explicit so readers can verify what each result measures.