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)|
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|
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.