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”:

Id | ItemId | Price | PriceRating —|———|—––|———–– 1 | 100 | 34.5 | EXPENSIVE 2 | 145 | 2.3 | CHEAP 3 | 100 | 34.5 | EXPENSIVE 4 | 100 | 34.5 | EXPENSIVE 5 | 145 | 10 | AFFORDABLE

Query

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

Results:

ItemsCount | ExpensiveItemsCount —— | —— 5 | 3

Alternative:

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