SQL: number of accounts per branch

Query

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:

branch_name  | no_of_accounts
Lakeside     | 1
Putalisadak  | 2

Variations

• Include branches that have no account:

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.)