Fee Summary Query in MySQL

A fee report

A fee summary is a core school-management report: how much each student has paid, how much is due, and the total collection. It is built with SUM and CASE (conditional aggregation) plus a JOIN for student names — a very practical query.

The tables

students(id, name)
fees(id, student_id, amount, status, paid_date)
-- status is 'paid' or 'due'

Storing one row per fee item, with a status and date, lets us total by status, list dues, and group by month — all from simple queries.

Paid and due per student

SELECT s.name,
    SUM(CASE WHEN f.status = 'paid' THEN f.amount ELSE 0 END) AS paid,
    SUM(CASE WHEN f.status = 'due'  THEN f.amount ELSE 0 END) AS due
FROM students s
JOIN fees f ON f.student_id = s.id
GROUP BY s.id, s.name;

Two CASE-SUMs split the amount into paid and due columns, grouped per student.

Only students with dues

SELECT s.name,
    SUM(CASE WHEN f.status = 'due' THEN f.amount ELSE 0 END) AS due
FROM students s
JOIN fees f ON f.student_id = s.id
GROUP BY s.id, s.name
HAVING due > 0;

Adding HAVING due > 0 keeps only students who still owe money — exactly the list an office needs for fee reminders.

Overall collection

SELECT SUM(amount) AS total_collected
FROM fees
WHERE status = 'paid';

For the school-wide figure, simply sum all paid amounts. This is the total money actually collected.

Collection by month

SELECT DATE_FORMAT(paid_date, '%Y-%m') AS month,
    SUM(amount) AS collected
FROM fees
WHERE status = 'paid'
GROUP BY DATE_FORMAT(paid_date, '%Y-%m')
ORDER BY month;

Grouping by the year-month of the payment date gives a monthly collection report — useful for tracking cash flow over the year.

Common mistakes

• Storing paid/due as one total. Keep per-item rows with a status for flexible reports.
• Using WHERE for the due filter. The due total is an aggregate — use HAVING.
• Forgetting ELSE 0 in CASE. Without it, SUM may include NULLs oddly.
• Not grouping by name and id. Group by the student key to be safe.

Summary

• Store one fee row per item with a status and date.
• Use SUM(CASE ...) to split paid and due per student.
• HAVING due > 0 lists students who still owe.
• SUM paid amounts for total collection.
• GROUP BY month for a monthly collection report.

Next, learn conditional totals in the pivot table, or build the full school database.