Society Maintenance Dues Collected per Wing — SQL Problem
A housing society's treasurer reports the March 2025 maintenance collection wing by wing.
- Difficulty: Easy
- Topics: Joins, Aggregation
- Dialect: MySQL
- Problem: #354
Problem statement
A housing society's treasurer reports the March 2025 maintenance collection wing by wing. A flat may pay its dues in more than one instalment, and some flats have not paid at all.
Return one row per wing in Flat with wing, flats (the number of flats in the wing), paid_flats (flats with at least one payment for '2025-03') and collected (the total paid for '2025-03', 0 for a wing with no payment). Payments for other months are ignored. Order the rows by wing.
Tables
Table: Flat
| Column | Type |
|---|---|
| flat_no | varchar |
| wing | char |
| area_sqft | int |
Primary key: flat_no.
Every flat of the society.
Table: DuesPayment
| Column | Type |
|---|---|
| payment_id | int |
| flat_no | varchar |
| dues_month | char |
| amount | int |
Primary key: payment_id.
dues_month is the month paid for, as 'YYYY-MM'. flat_no is always a flat in Flat.
Examples
Example 1
Flat
| flat_no | wing | area_sqft |
|---|---|---|
| A-101 | A | 950 |
| A-102 | A | 1100 |
| A-201 | A | 950 |
| B-101 | B | 1250 |
| B-102 | B | 1250 |
| C-101 | C | 800 |
DuesPayment
| payment_id | flat_no | dues_month | amount |
|---|---|---|---|
| 1 | A-101 | 2025-03 | 3000 |
| 2 | A-101 | 2025-03 | 1750 |
| 3 | A-102 | 2025-03 | 5500 |
| 4 | B-101 | 2025-02 | 6250 |
| 5 | B-102 | 2025-03 | 6250 |
| 6 | A-201 | 2025-02 | 4750 |
Output
| wing | flats | paid_flats | collected |
|---|---|---|---|
| A | 3 | 2 | 10250 |
| B | 2 | 1 | 6250 |
| C | 1 | 0 | 0 |
How to solve Society Maintenance Dues Collected per Wing
The answer has a row for every wing, paid or not, so the query starts from Flat and LEFT JOINs the March payments. The month condition goes into the ON clause: it restricts which payments may match while every flat survives — a flat with no March payment comes through once with NULLs.
Two things change the counts. A flat that paid in two instalments matches two payment rows, so COUNT(*) would count it twice; COUNT(DISTINCT f.flat_no) counts flats, and COUNT(DISTINCT p.flat_no) counts only flats that matched a payment, because NULLs are not counted. And a wing where nobody paid sums to NULL, which COALESCE(…, 0) turns into 0, as wing C shows.
Pre-aggregating payments per flat in a derived table first avoids the duplication altogether — then every flat is one row and plain counts work. Correlated subqueries per wing are a third way, slower but clear.
Reference solution (MySQL)
SELECT f.wing,
COUNT(DISTINCT f.flat_no) AS flats,
COUNT(DISTINCT p.flat_no) AS paid_flats,
COALESCE(SUM(p.amount), 0) AS collected
FROM Flat f
LEFT JOIN DuesPayment p
ON p.flat_no = f.flat_no AND p.dues_month = '2025-03'
GROUP BY f.wing
ORDER BY f.wingAnother way
SELECT w.wing, (SELECT COUNT(*) FROM Flat x WHERE x.wing = w.wing) AS flats, (SELECT COUNT(DISTINCT p.flat_no) FROM DuesPayment p JOIN Flat x ON x.flat_no = p.flat_no WHERE x.wing = w.wing AND p.dues_month = '2025-03') AS paid_flats, (SELECT IFNULL(SUM(p.amount), 0) FROM DuesPayment p JOIN Flat x ON x.flat_no = p.flat_no WHERE x.wing = w.wing AND p.dues_month = '2025-03') AS collected FROM (SELECT DISTINCT wing FROM Flat) w ORDER BY w.wingAnother way
SELECT f.wing, COUNT(*) AS flats, SUM(CASE WHEN t.paid IS NULL THEN 0 ELSE 1 END) AS paid_flats, COALESCE(SUM(t.paid), 0) AS collected FROM Flat f LEFT JOIN (SELECT flat_no, SUM(amount) AS paid FROM DuesPayment WHERE dues_month = '2025-03' GROUP BY flat_no) t ON t.flat_no = f.flat_no GROUP BY f.wing ORDER BY f.wing← Broker Commission by Deal Slab · Leases Ending Within the Next 30 Days →