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

Photo by Crissy Jarvis, https://unsplash.com/photos/gdL-UZfnD3I

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.