Write a query to show the total balance using debit and credit entries

Description: A table transactions has the columns id, account_id, type (credit or debit) and amount. Show the balance of each account. Credit adds money and debit takes money.

Hint:

• Use CASE to turn a debit into a negative amount.
• Then SUM() the amounts with GROUP BY account_id.

---

MySQL query for the balance from debit and credit

-- sample data
CREATE TABLE transactions (id INT, account_id INT, type VARCHAR(10), amount INT);
INSERT INTO transactions VALUES
  (1, 1, 'credit', 1000),
  (2, 1, 'debit', 200),
  (3, 2, 'credit', 500),
  (4, 2, 'debit', 100),
  (5, 1, 'credit', 300);

-- the query
SELECT account_id,
       SUM(CASE WHEN type = 'credit' THEN amount ELSE -amount END) AS balance
FROM transactions
GROUP BY account_id
ORDER BY account_id;

Output:

How it works

• For account 1: 1000 – 200 + 300 = 1100.
• For account 2: 500 – 100 = 400.
• CASE works like if else inside a query.

• Next Exercise

---

Tutorials
No Content Found.

Exercises & Assignments
No Content Found.

Interview Questions & Answers
No Content Found.