---
title: "SQL: number of accounts per branch"
subject: "Database Management System"
chapter: 3
key: "semester_6/database_management_system:sql-count-accounts-branch"
source: https://www.bctengineer.com/q/semester_6/database_management_system/sql-count-accounts-branch
site: BCT Engineering
---

# SQL: number of accounts per branch

Database Management System · Chapter 3: Relational Languages & Relational Model

- Asked in: year not given
- Video: [Lec-79: SQL Aggregate Functions - SUM, AVG(n), COUNT, MIN, MAX Functions | DBMS](https://www.youtube.com/watch?v=Yr4pHPZCshA) — Gate Smashers
- Answer checked by Claude Opus 5.5 (high effort) on 2026-10-14
- Page: https://www.bctengineer.com/q/semester_6/database_management_system/sql-count-accounts-branch
- Whole chapter: https://www.bctengineer.com/md/semester_6/database_management_system/chapter-3

## Question

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 no. of accounts in each branch.

## Short answer

Use SELECT branch_name, COUNT(acc_no) AS no_of_accounts FROM Account GROUP BY branch_name. This counts accounts in each branch.

## Answer

#### Query
```sql
SELECT branch_name, COUNT(acc_no) AS no_of_accounts
FROM Account
GROUP BY branch_name;
```

#### Explanation
1. GROUP BY branch_name puts all rows of the Account table with the same branch in one group.
2. COUNT(acc_no) counts the accounts in each group.
3. The alias no_of_accounts names the result column.

#### Sample output
For accounts 101 and 102 in Putalisadak, and 103 in Lakeside:
```text
branch_name  | no_of_accounts
Lakeside     | 1
Putalisadak  | 2
```

#### Variations
- Include branches that have no account:
```sql
SELECT b.branch_name, COUNT(a.acc_no) AS no_of_accounts
FROM Branch b LEFT JOIN Account a ON a.branch_name = b.branch_name
GROUP BY b.branch_name;
```
- Only branches with more than 5 accounts: add `HAVING COUNT(acc_no) > 5`.

(Checked on a sample database in SQLite.)

## Check yourself

The answer's key points as multiple-choice questions, with the correct option and why.

1. What does GROUP BY branch_name do?
   A. Sorts only
   B. Filters
   C. Deletes duplicates
   D. Puts rows of the same branch in one group
   Answer: D. Aggregates are computed per group. (Key point: Group by)

2. How to include branches with no accounts?
   A. INNER JOIN
   B. LEFT JOIN Branch with Account and COUNT(a.acc_no)
   C. Use HAVING
   D. Use WHERE
   Answer: B. Left join keeps all branches; COUNT of NULL gives 0. (Key point: Include empty branches)

3. How to keep branches with more than 5 accounts?
   A. WHERE COUNT(acc_no) > 5
   B. ORDER BY 5
   C. LIMIT 5
   D. HAVING COUNT(acc_no) > 5
   Answer: D. Aggregate conditions go in HAVING. (Key point: Having)

## Close variants set in other exams

- [SQL: customer name and address](https://www.bctengineer.com/question.md?key=semester_6%2Fdatabase_management_system%3Asql-customer-name-address) — Database Management System, chapter 3
- [SQL: customer names beginning with su](https://www.bctengineer.com/question.md?key=semester_6%2Fdatabase_management_system%3Asql-customer-name-like-su) — Database Management System, chapter 3
- [SQL: branch city with assets over 1000000](https://www.bctengineer.com/question.md?key=semester_6%2Fdatabase_management_system%3Asql-branch-assets-million) — Database Management System, chapter 3
- [SQL: customer, branch, account details](https://www.bctengineer.com/question.md?key=semester_6%2Fdatabase_management_system%3Asql-natural-join-details) — Database Management System, chapter 3
- [SQL: branches with avg balance over 50000](https://www.bctengineer.com/question.md?key=semester_6%2Fdatabase_management_system%3Asql-avg-balance-branch) — Database Management System, chapter 3
- [SQL: increase balances by 100% and 50%](https://www.bctengineer.com/question.md?key=semester_6%2Fdatabase_management_system%3Asql-update-balance-rates) — Database Management System, chapter 3
- [SQL: customers with account in Dharan](https://www.bctengineer.com/question.md?key=semester_6%2Fdatabase_management_system%3Asql-customers-dharan-branch) — Database Management System, chapter 3

## Related in Database Management System

- [SQL: create tables for bank schema](https://www.bctengineer.com/question.md?key=semester_6%2Fdatabase_management_system%3Asql-create-tables-bank) — Database Management System, chapter 3
- [SQL: create and use bank database](https://www.bctengineer.com/question.md?key=semester_6%2Fdatabase_management_system%3Asql-create-database-bank) — Database Management System, chapter 3
- [RA: count accounts in each branch](https://www.bctengineer.com/question.md?key=semester_6%2Fdatabase_management_system%3Ara-count-accounts-branch) — Database Management System, chapter 3
- [RA: delete loans under 1000](https://www.bctengineer.com/question.md?key=semester_6%2Fdatabase_management_system%3Ara-delete-small-loans) — Database Management System, chapter 3
- [RA: increase loans over 10000](https://www.bctengineer.com/question.md?key=semester_6%2Fdatabase_management_system%3Ara-increase-loan-10) — Database Management System, chapter 3