Counting with an Inner Join.

[new2sql]

I have two tables.

TBL_PRACTICE
Practice_ID (primary key)
Practice_Name
etc.

TBL_BRANCH
Branch_ID (primary key)
Branch_Name
Practice_ID
etc.

I need to create a view that has 2 fields. Practice Name, Number of Branches.

I have the following SQL, but this counts all the branches, not just the branches for this particular practice.

SELECT     TBL_PRACTICE.Practice_ID, TBL_PRACTICE.Practice_Name,
    (SELECT count(TBL_BRANCH.Branch_ID) FROM TBL_BRANCH) AS Branches
FROM TBL_PRACTICE INNER JOIN
    TBL_BRANCH ON TBL_PRACTICE.Practice_ID = TBL_BRANCH.Practice_ID

If someone could let me know how I count only the branches for the corresponding Practice.

Thanks in advance, much appriciated.

[mwasif]

Use COUNT() and GROUP BY

SELECT TBL_PRACTICE.Practice_ID, TBL_PRACTICE.Practice_Name, COUNT(TBL_BRANCH.Branch_ID) AS Branches
FROM TBL_PRACTICE INNER JOIN
TBL_BRANCH ON TBL_PRACTICE.Practice_ID = TBL_BRANCH.Practice_ID
GROUP BY Practice_Name