Society Maintenance Dues Collected per Wing

Easy
354.

Society Maintenance Dues Collected per Wing

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

ColumnType
flat_novarchar
wingchar
area_sqftint

Primary key: flat_no.

Every flat of the society.

Table: DuesPayment

ColumnType
payment_idint
flat_novarchar
dues_monthchar
amountint

Primary key: payment_id.

dues_month is the month paid for, as 'YYYY-MM'. flat_no is always a flat in Flat.

Example

The rows must come back in the order shown.

Flat

flat_nowingarea_sqft
A-101A950
A-102A1100
A-201A950
B-101B1250
B-102B1250
C-101C800

DuesPayment

payment_idflat_nodues_monthamount
1A-1012025-033000
2A-1012025-031750
3A-1022025-035500
4B-1012025-026250
5B-1022025-036250
6A-2012025-024750

Output

wingflatspaid_flatscollected
A3210250
B216250
C100
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
flat_novarchar
wingchar
area_sqftint

Primary key: flat_no.

Every flat of the society.

Table: DuesPayment

ColumnType
payment_idint
flat_novarchar
dues_monthchar
amountint

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_nowingarea_sqft
A-101A950
A-102A1100
A-201A950
B-101B1250
B-102B1250
C-101C800

DuesPayment

payment_idflat_nodues_monthamount
1A-1012025-033000
2A-1012025-031750
3A-1022025-035500
4B-1012025-026250
5B-1022025-036250
6A-2012025-024750

Output

wingflatspaid_flatscollected
A3210250
B216250
C100

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

Another 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.wing

Another 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 →