如何在SQL的Count函数中使用Like统计活跃志愿者数量

云原生阿岚

2026-5-25

Fixing Your Active Volunteer Count Query

Hey there! Let's sort out that SQL query for you. The issue with your current code is that COUNT() counts any non-null value, and the expression VolunteerCategory LIKE '%Active%' returns a boolean (treated as 1/true or 0/false in most SQL dialects)—but COUNT() doesn't skip the 0 values, it counts them all as valid entries. That's why it isn't giving you the accurate number of active volunteers per funder.

Here are two reliable approaches to get the result you want:

1. Use `SUM()` with a Conditional Check

This method is perfect if you want to include all funder IDs (even those with 0 active volunteers):

SELECT 
  VolunteersFunderID,
  SUM(CASE WHEN VolunteerCategory LIKE '%Active%' THEN 1 ELSE 0 END) AS NumberActive
FROM VolunteerTbl
GROUP BY VolunteersFunderID

The CASE statement returns 1 for every active volunteer and 0 for non-active ones. SUM() then adds those values up to get the total active volunteers per funder.

2. Filter Rows First with `WHERE` (For Funders With Active Volunteers Only)

If you don't need to show funders that have no active volunteers, you can filter the dataset before grouping—this is also more efficient:

SELECT 
  VolunteersFunderID,
  COUNT(*) AS NumberActive
FROM VolunteerTbl
WHERE VolunteerCategory LIKE '%Active%'
GROUP BY VolunteersFunderID

This reduces the number of rows being grouped, but it won't include funders with zero active volunteers in the results.

Quick Tip

Double-check that VolunteerCategory doesn't have extra whitespace (like ' Active' or 'Active ') that would make the LIKE check fail. If whitespace is a potential issue, use TRIM() to clean up the value first: