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.