# SQL: Counting with Conditions

*Three different ways in SQL for counting rows that satisfy certain conditions.*

Given the following sample table

Name | Height (cm) | Weight (kg) |
---|---|---|

Alice | 150 | 50 |

Bob | 160 | 60 |

Charlie | 170 | 70 |

we want to write an SQL query that asks the following questions:

- How many persons have a height >= 160?
- How many persons have a weight >= 70?

The answers shall be given in a single table like this:

# with Height >= 160 | # with Weight >= 70 |
---|---|

2 | 1 |

Here are three different solutions, ordered by descending length:

```
-- Count with subselect:
SELECT
(SELECT COUNT(*) FROM persons WHERE height >= 160) AS height_count,
(SELECT COUNT(*) FROM persons WHERE weight >= 70) AS weight_count;
-- Count with case:
SELECT
COUNT(CASE WHEN height >= 160 THEN 1 END) AS height_count,
COUNT(CASE WHEN weight >= 70 THEN 1 END) AS weight_count
FROM persons;
-- Sum with cast from bool to int:
SELECT
SUM((height >= 160)::int) AS height_count,
SUM((weight >= 70)::int) AS weight_count
FROM persons;
```

Tested with PostgreSQL 12.