---
title: "How to count the number of rows that match a condition in Redshift"
description: "How to count the rows by multiple conditions at the same time in SQL"
author: "Bartosz Mikulski"
author_bio: "Principal AI Engineer & MLOps Architect. I bridge the gap between \"it works in a notebook\" and \"it works for 200 million users.\""
author_url: https://mikulskibartosz.name
author_linkedin: https://www.linkedin.com/in/mikulskibartosz/
author_github: https://github.com/mikulskibartosz
canonical_url: https://mikulskibartosz.name/count-by-condition-in-redshift
---

It is trivial to count the rows that match a single condition in SQL. We use the WHERE clause and put the condition there. However, how would we count the rows when we want to check multiple conditions simultaneously?

The one way to do that is to use the CASE expression to define the condition and list them as separate columns. For example, if I have a table of product prices `products` and I want to count the number of products in a few price ranges, I have to do it like this:

```sql
SELECT
    count(CASE WHEN price < 50 THEN 1 END) as price_less_than_50,
    count(CASE WHEN grade >= 50 and grade < 80 THEN 1 END) as price_between_50_80,
    count(CASE WHEN price >= 80 THEN 1 END) as price_80_or_more,
FROM
   products
```

## How does it work?

The `count` function counts the number of non-empty rows, and the `CASE` expression returns either one or the default value `null`. Therefore, when the `CASE` matches the expression, the `count` function gets one and counts the row as matching the condition. Otherwise, `null` is passed to the `count` function, and the function ignores all nulls.