SQL GROUP BY
The group by command allows you to categorize rows based on one or more columns to calculate things like average, max value, min value, etc, for each category.
-- 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
In this example, we used the COUNT() aggregate function. This counted the occurence of each unique value in the tenant_id column.
We could have used another aggregate function to get a different result, such as: