SQL COUNT Tutorial
The COUNT command is used to count how many rows are in a given result set. It is frequently used with the GROUP BY command.
Example 1 (no aggregation)
-- Works in PostgreSQL, MySQL, SQL Server, and Oracle
SELECT
COUNT(*) as num_payments
FROM
rent_payments
WHERE tenant_id = 1;
rent_payments table
| id | tenant_id | payment_date | dollar_amount |
|---|---|---|---|
| 1 | 1 | 3/1/2018 | 2000 |
| 2 | 2 | 3/2/2018 | 1500 |
| 3 | 3 | 3/1/2018 | 1800 |
| 4 | 4 | 3/3/2018 | 1900 |
| 5 | 1 | 4/2/2018 | 2000 |
| 6 | 4 | 4/2/2018 | 1900 |
Query results
| num_payments |
|---|
| 2 |
Example 2 (with aggregation)
-- Works in PostgreSQL, MySQL, SQL Server, and Oracle
SELECT
tenant_id,
COUNT(*) as num_payments
FROM
rent_payments
GROUP BY
tenant_id;
rent_payments table
| id | tenant_id | payment_date | dollar_amount |
|---|---|---|---|
| 1 | 1 | 3/1/2018 | 2000 |
| 2 | 2 | 3/2/2018 | 1500 |
| 3 | 3 | 3/1/2018 | 1800 |
| 4 | 4 | 3/3/2018 | 1900 |
| 5 | 1 | 4/2/2018 | 2000 |
| 6 | 4 | 4/2/2018 | 1900 |
Query results
| tenant_id | num_payments |
|---|---|
| 1 | 2 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
Explanation
We are counting the occurence of each unique value in the tenant_id column.