How Can I Count Rows Meeting Specific Conditions in SQL Using COUNT()?

Use the SQL COUNT() function to count the number of rows that meet specific conditions

The COUNT() aggregate function in SQL usually counts all non-null values ​​in a column. But what if you only want to count the number of rows that meet certain criteria? For example, only count the number of employees with the title "Manager" in the job column.

Although the WHERE clause provides a straightforward way to filter rows, it may not be sufficient in situations where you need to count both managers and other employees. At this time, the conditional expression in the COUNT() aggregate function comes in handy.

Use conditional expressions in COUNT()

You can take advantage of the feature of COUNT() to count only non-null values ​​instead of using the WHERE clause. By using conditional expressions, you can create a case statement that evaluates the Position column and assigns a specific value based on a condition.

Example 1: Using Case and Count()

select count(case Position when 'Manager' then 1 else null end)
from ...

In this query, the case statement evaluates whether the Position column is equal to 'Manager'. If true, the value assigned is 1; otherwise, the value assigned is null. The COUNT() aggregate function then sums all non-null values ​​in the case expression to get the number of manager rows.

Example 2: Using Case and Sum()

Alternatively, you can use the Sum() aggregate function and similar conditional expressions:

select sum(case Position when 'Manager' then 1 else 0 end)
from ...

This method can also get the number of manager rows because the Sum() aggregate function sums the values ​​specified in the case expression and effectively counts non-zero values ​​(i.e. rows with Position equal to 'Manager').

The above is the detailed content of How Can I Count Rows Meeting Specific Conditions in SQL Using COUNT()?. For more information, please follow other related articles on the PHP Chinese website!