Monthly Fee Collection by Payment Mode — SQL Dates Problem
The college bursar reports fee collections each month split by how the money came in, to reconcile against the UPI settlement and card gateway statements.
- Difficulty: Medium
- Topics: Dates, Conditional Logic, Aggregation
- Dialect: MySQL
- Problem: #132
Problem statement
The college bursar reports fee collections each month split by how the money came in, to reconcile against the UPI settlement and card gateway statements.
Return one row per calendar month that has at least one payment, with the columns month (as 'YYYY-MM'), upi_amount, card_amount, other_amount (net banking and cash together) and total_amount, all in rupees. A mode with no payments in a month shows 0, never NULL. Order the rows by month.
Tables
Table: FeePayment
| Column | Type |
|---|---|
| payment_id | int |
| roll_no | int |
| amount | int |
| paid_on | date |
| mode | enum(UPI, Card, NetBanking, Cash) |
Primary key: payment_id.
One row per successful payment; amount is in rupees.
Examples
Example 1
FeePayment
| payment_id | roll_no | amount | paid_on | mode |
|---|---|---|---|---|
| 1 | 2101 | 42000 | 2024-07-03 | UPI |
| 2 | 2102 | 42000 | 2024-07-05 | Card |
| 3 | 2103 | 85000 | 2024-07-29 | NetBanking |
| 4 | 2104 | 12500 | 2024-08-01 | UPI |
| 5 | 2101 | 12500 | 2024-08-14 | UPI |
| 6 | 2105 | 3000 | 2024-08-20 | Cash |
| 7 | 2106 | 85000 | 2024-10-02 | Card |
Output
| month | upi_amount | card_amount | other_amount | total_amount |
|---|---|---|---|---|
| 2024-07 | 42000 | 42000 | 85000 | 169000 |
| 2024-08 | 25000 | 0 | 3000 | 28000 |
| 2024-10 | 0 | 85000 | 0 | 85000 |
How to solve Monthly Fee Collection by Payment Mode
One row per month means grouping by the month, and DATE_FORMAT(paid_on, '%Y-%m') produces exactly the label the statement wants. Grouping by MONTH(paid_on) alone would merge July 2024 with July 2025, so keep the year in the key.
The split by mode is conditional aggregation: inside the same group, SUM(CASE WHEN mode = 'UPI' THEN amount ELSE 0 END) adds only the UPI rows, and so on for each column. ELSE 0 is what makes a mode with no payments that month read 0; without it the CASE yields NULL for every row and the SUM is NULL — the third query shows the COALESCE fix instead.
total_amount is a plain SUM(amount), which equals the three mode columns added up because every payment has exactly one mode. Months with no payments simply have no group, so they do not appear. The whole report is one pass over the payments.
Reference solution (MySQL)
SELECT DATE_FORMAT(paid_on, '%Y-%m') AS month,
SUM(CASE WHEN mode = 'UPI' THEN amount ELSE 0 END) AS upi_amount,
SUM(CASE WHEN mode = 'Card' THEN amount ELSE 0 END) AS card_amount,
SUM(CASE WHEN mode IN ('NetBanking', 'Cash') THEN amount ELSE 0 END) AS other_amount,
SUM(amount) AS total_amount
FROM FeePayment
GROUP BY DATE_FORMAT(paid_on, '%Y-%m')
ORDER BY monthAnother way
SELECT m AS month, SUM(IF(mode = 'UPI', amount, 0)) AS upi_amount, SUM(IF(mode = 'Card', amount, 0)) AS card_amount, SUM(IF(mode = 'UPI' OR mode = 'Card', 0, amount)) AS other_amount, SUM(amount) AS total_amount FROM (SELECT CONCAT(YEAR(paid_on), '-', LPAD(MONTH(paid_on), 2, '0')) AS m, mode, amount FROM FeePayment) t GROUP BY m ORDER BY mAnother way
SELECT DATE_FORMAT(paid_on, '%Y-%m') AS month, COALESCE(SUM(CASE WHEN mode = 'UPI' THEN amount END), 0) AS upi_amount, COALESCE(SUM(CASE WHEN mode = 'Card' THEN amount END), 0) AS card_amount, COALESCE(SUM(CASE WHEN mode IN ('NetBanking', 'Cash') THEN amount END), 0) AS other_amount, SUM(amount) AS total_amount FROM FeePayment GROUP BY 1 ORDER BY 1← First Student to Submit Each Assignment · Courses Where More Than a Quarter of Students Failed →