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.