---
title: "RA: count accounts in each branch"
subject: "Database Management System"
chapter: 3
key: "semester_6/database_management_system:ra-count-accounts-branch"
source: https://www.bctengineer.com/q/semester_6/database_management_system/ra-count-accounts-branch
site: BCT Engineering
---

# RA: count accounts in each branch

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

- Asked in: year not given
- Video: [DBMS - Aggregate Function in Relational Algebra](https://www.youtube.com/watch?v=pVVKjbCW1e8) — TutorialsPoint
- Answer checked by Claude Opus 5.5 (high effort) on 2026-10-14
- Page: https://www.bctengineer.com/q/semester_6/database_management_system/ra-count-accounts-branch
- Whole chapter: https://www.bctengineer.com/md/semester_6/database_management_system/chapter-3

## Question

Consider the relational schema:
Acc (a_no, b_name, bal)
Branch (b_name, b_city, assets)
Customer (c_name, c_street, c_city)
Loan (l_no, b_name, amt)
Depositor (c_name, a_no)
Borrower (c_name, l_no)
Count number of accounts in each branch. (Write a relational algebra expression.)

## Short answer

Group Acc tuples by b_name and apply count(a_no) aggregate. Rename the result to show branch name and number of accounts.

## Answer

Relational algebra symbols used: σ select, π project, ⋈ natural join, ∪ union, ∩ intersection, − difference, ρ rename, 𝒢 aggregate.

Counting needs the aggregate (generalized) operator 𝒢, written as:  *grouping attributes* 𝒢 *aggregate function(attribute)* (relation).

#### Relational algebra expression
```text
b_name 𝒢 count(a_no) (Acc)
```
Renaming the result column:
```text
ρ branch_count(b_name, no_of_accounts) ( b_name 𝒢 count(a_no) (Acc) )
```

#### Explanation
- Acc tuples are grouped by b_name, one group per branch.
- count(a_no) counts the account numbers in each group.
- The output has one tuple per branch: (b_name, number of accounts).

Example: if Acc has accounts 101, 102 in "Putalisadak" and 103 in "Lakeside", the result is (Putalisadak, 2), (Lakeside, 1).

A branch with no account does not appear in the result. To list it with 0, a left outer join with Branch would be needed.

#### Equivalent SQL
```sql
SELECT b_name, COUNT(a_no) FROM Acc GROUP BY b_name;
```

## Check yourself

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

1. Which operator is needed for counting?
   A. Natural join
   B. Selection
   C. The aggregate (generalized) operator 𝒢
   D. Difference
   Answer: C. Counting uses grouping with aggregate functions. (Key point: Aggregate operator)

2. In b_name 𝒢 count(a_no)(Acc), what is b_name?
   A. The grouping attribute, one group per branch
   B. The result column only
   C. The counted attribute
   D. A selection condition
   Answer: A. Attributes left of 𝒢 are grouping attributes. (Key point: Expression meaning)

3. What happens to a branch with no accounts?
   A. It appears with 0
   B. It causes an error
   C. It does not appear; a left outer join with Branch would be needed
   D. It appears with NULL name
   Answer: C. Grouping only forms groups from existing tuples. (Key point: Empty branch)

## Related in Database Management System

- [SQL: number of accounts per branch](https://www.bctengineer.com/question.md?key=semester_6%2Fdatabase_management_system%3Asql-count-accounts-branch) — 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
- [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
- [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, branch, account details](https://www.bctengineer.com/question.md?key=semester_6%2Fdatabase_management_system%3Asql-natural-join-details) — Database Management System, chapter 3