How Can I Use COUNT() to Count Rows Based on a Condition?

Conditional counting in the COUNT() function

In some cases it may be necessary to count rows based on specific criteria instead of getting the total count. For example, we might want to count only those rows that have a specific value in a specific column.

Using the COUNT() function, you can specify conditions to filter rows for counting. This can be achieved by using a CASE expression in conjunction with the COUNT() function, as shown in the following code snippet:

<code class="language-sql">select count(case Position when 'Manager' then 1 else null end)
from ...</code>

In this example, we only count rows where the value of the 'Position' column is 'Manager'. The CASE expression evaluates the 'Position' column of each row. Returns 1 if the column value matches 'Manager'; otherwise, returns null. This ensures that only rows containing 'Manager' in the 'Position' column are counted.

Alternatively, you can use the SUM() function similarly:

<code class="language-sql">select sum(case Position when 'Manager' then 1 else 0 end)
from ...</code>

This method also uses CASE expressions to filter rows. However, for unmatched rows, it returns 0 instead of null. The SUM() function then calculates the sum of these values, giving the number of rows that contain 'Manager' in the 'Position' column.

The above is the detailed content of How Can I Use COUNT() to Count Rows Based on a Condition?. For more information, please follow other related articles on the PHP Chinese website!