Monthly Fee Collection by Payment Mode

Medium
132.

Monthly Fee Collection by Payment Mode

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

ColumnTypeValues
payment_idint
roll_noint
amountint
paid_ondate
modeenumUPI, Card, NetBanking, Cash

Primary key: payment_id.

One row per successful payment; amount is in rupees.

Example

The rows must come back in the order shown.

FeePayment

payment_idroll_noamountpaid_onmode
12101420002024-07-03UPI
22102420002024-07-05Card
32103850002024-07-29NetBanking
42104125002024-08-01UPI
52101125002024-08-14UPI
6210530002024-08-20Cash
72106850002024-10-02Card

Output

monthupi_amountcard_amountother_amounttotal_amount
2024-07420004200085000169000
2024-08250000300028000
2024-10085000085000
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.

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

ColumnType
payment_idint
roll_noint
amountint
paid_ondate
modeenum(UPI, Card, NetBanking, Cash)

Primary key: payment_id.

One row per successful payment; amount is in rupees.

Examples

Example 1

FeePayment

payment_idroll_noamountpaid_onmode
12101420002024-07-03UPI
22102420002024-07-05Card
32103850002024-07-29NetBanking
42104125002024-08-01UPI
52101125002024-08-14UPI
6210530002024-08-20Cash
72106850002024-10-02Card

Output

monthupi_amountcard_amountother_amounttotal_amount
2024-07420004200085000169000
2024-08250000300028000
2024-10085000085000

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 month

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

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