CODEWITHSUNDEEP
sql
user5582406
user5582406

Reputation:

Like inside a count SQL

I want to use a Like Inside a Count So i can take the Number of (in this example) number of active Volunteers along side the VolunteerFundersID

Here is what i have although something tells me I'm going completely the wrong way about it . 

select
    VolunteersFunderID,
    Count( VolunteerCategory LIKE '%Active%' ) as NumberActive
from
    VolunteerTbl
group BY
    VolunteersFunderID

I'm new to using this website and any feedback that isn't insults (I got the last time i posted) would be hugely appreciated.

Upvotes: 0

Views: 2192

Answers (3)

robertus
robertus

Reputation: 356

Count is aggregate function, so it "counts" only not NULL values. Knowing that you can use CASE to make NULL and NOT NULL values to count only active one.

Query below should work:

select VolunteersFunderID, 
    Count(
          case when VolunteerCategory LIKE '%Active%' 
              then 1 
              else NULL
          end
    ) as NumberActive
from VolunteerTbl
group BY VolunteersFunderID

It is worth mentioning:

  • using like with wildcard % at the beginning makes query slower - are you sure you cannot just write VolunteerCategory = 'Active' ?
  • are you sure that other values in that field will not interfere with your "like condition"? I could imagine other VolunteerCategory e.g. InActive.

Upvotes: 0

clinomaniac
clinomaniac

Reputation: 2218

There are a few different ways to do this and you need to choose what works best for your scenario. 

select VolunteersFunderID,
    Count(CASE WHEN VolunteerCategory LIKE '%Active%' THEN 1 END) as NumberActive
from VolunteerTbl
group BY VolunteersFunderID

This will go through all rows and count any record where VolunteerCategory is Active. This way, you can count for more categories if you need to do so and put additional conditions etc because you are looking at the full data set.

Another way is to filter the data using a WHERE clause:

select VolunteersFunderID,Count(1) as NumberActive
from VolunteerTbl
WHERE VolunteerCategory LIKE '%Active%'
group BY VolunteersFunderID

Using the WHERE clause, you are filtering your data before you calculate anything so its harder to do other computations.

Upvotes: 2

zipa
zipa

Reputation: 27869

Move it inside WHERE clause:

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

Upvotes: 0

Related Questions