SQL Use CASE to COUNT the number of rows in a column match a condition.


Example

Use Case

CASE can be used in conjunction with SUM to return a count of only those items matching a pre-defined condition. (This is similar to COUNTIF in Excel.)

The trick is to return binary results indicating matches, so the "1"s returned for matching entries can be summed for a count of the total number of matches.

Given this table ItemSales, let's say you want to learn the total number of items that have been categorized as "Expensive":

IdItemIdPricePriceRating
110034.5EXPENSIVE
21452.3CHEAP
310034.5EXPENSIVE
410034.5EXPENSIVE
514510AFFORDABLE

Query

SELECT 
    COUNT(Id) AS ItemsCount,
    SUM ( CASE 
            WHEN PriceRating = 'Expensive' THEN 1
            ELSE 0
          END
        ) AS ExpensiveItemsCount
FROM ItemSales 

Results:

ItemsCountExpensiveItemsCount
53

Alternative:

SELECT 
    COUNT(Id) as ItemsCount,
    SUM (
        CASE PriceRating 
            WHEN 'Expensive' THEN 1
            ELSE 0
        END
       ) AS ExpensiveItemsCount
FROM ItemSales