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":
SELECT COUNT(Id) AS ItemsCount, SUM ( CASE WHEN PriceRating = 'Expensive' THEN 1 ELSE 0 END ) AS ExpensiveItemsCount FROM ItemSales
SELECT COUNT(Id) as ItemsCount, SUM ( CASE PriceRating WHEN 'Expensive' THEN 1 ELSE 0 END ) AS ExpensiveItemsCount FROM ItemSales