Skip to main content

SQL: branches with avg balance over 50000

Short answer

Use SELECT branch_name FROM Account GROUP BY branch_name HAVING AVG(balance) > 50000. HAVING filters groups after averaging balances.

Consider database schema: Account (acc_no, branch_name, balance) Branch (branch_name, branch_city, assets) Customer (customer_name, customer_street, customer_city) Loan (loan_no, branch_name, amount) Depositor (customer_name, acc_no) Borrower (customer_name, loan_no) List branch name where avg acc balance is more than 50000.

Group the accounts by branch, compute the average balance of each group, and keep only the groups that satisfy the condition. A condition on an aggregate must go in HAVING, not WHERE, because WHERE filters rows before grouping.

SELECT   branch_name
FROM     Account
GROUP BY branch_name
HAVING   AVG(balance) > 50000;

To also show the average:

SELECT   branch_name, AVG(balance) AS avg_balance
FROM     Account
GROUP BY branch_name
HAVING   AVG(balance) > 50000;

How it executes

  1. GROUP BY branch_name puts all accounts of one branch into one group.
  2. AVG(balance) is computed for every group.
  3. HAVING removes the groups whose average is 50000 or less.

Sample (tested): Dharan has balances 60000 and 70000, Pokhara has 5000 and 9000, Itahari has 10000 and 20000. Only Dharan is returned (average 65000).

✓ Answer checked by Claude Opus 5.5 (high effort)

Related questions

All Database Management System chapter 3 questions →