SQLServer CASE
Use CASE to COUNT the number of rows in a column match a condition
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.
Query
SELECT
COUNT(Id) AS ItemsCount,
SUM ( CASE
WHEN PriceRating = 'Expensive' THEN 1
ELSE 0
END
) AS ExpensiveItemsCount
FROM ItemSalesResults:
ItemsCount ExpensiveItemsCount
5 3
Alternative:
SELECT
COUNT(Id) as ItemsCount,
SUM (
CASE PriceRating
WHEN 'Expensive' THEN 1
ELSE 0
END
) AS ExpensiveItemsCount
FROM ItemSales