SQL Return Table with where clause and occurrences of value of total table

Question

I am stumped trying to figure out how to achieve this. I know how I would achieve it in C#, but not SQL.

Say I have the following table:

ID	Name	RouteId
1	Bob	1001
2	Bob	1002
3	Ana	1001
4	Jim	1001
5	Eli	1001

I would like to return the entire table, with an extra column showing the total occurrences of routeID by name, so where name='Bob' looks like:

ID	Name	RouteId	Total
1	Bob	1001	4
2	Bob	1002	1

However, if I write something like

declare @ct as nvarchar(5)
set @ct = (SELECT COUNT(RouteId) from <table>)

select *, @ct
from <table>
where name = 'Bob'

I get the total number of ALL route IDs, not just the one displayed in the row.

I tried looking into computed columns but from what I can see it doesn't support this type of query.

Can anyone point me in the right direction?

Answer 1

You can use a windowed COUNT inside a derived table (subquery)

SELECT *
FROM (
    SELECT *,
      Total = COUNT(*) OVER (PARTITION BY t.RouteId)
    FROM MyTable t
) t
WHERE t.Name = 'Bob';

db<>fiddle

Answer 2

Three more alternatives:

Subquery

SELECT 
    MT.*,
    Total =
        (
            SELECT COUNT_BIG(*) 
            FROM dbo.MyTable AS MT2
            WHERE
                MT2.RouteId = MT.RouteId
        )
FROM dbo.MyTable AS MT
WHERE 
    MT.[Name] = 'Bob';

Apply

SELECT 
    MT.*,
    A.Total
FROM dbo.MyTable AS MT
CROSS APPLY 
(
        SELECT Total = COUNT_BIG(*) 
        FROM dbo.MyTable AS MT2
        WHERE MT2.RouteId = MT.RouteID
        GROUP BY ()
) AS A
WHERE 
    MT.[Name] = 'Bob';

Join

SELECT 
    MT.*, 
    T.Total 
FROM dbo.MyTable AS MT
JOIN
(
    SELECT 
        MT2.RouteId, 
        Total = COUNT_BIG(*)
    FROM dbo.MyTable AS MT2
    GROUP BY MT2.RouteId
) AS T
    ON T.RouteId = MT.RouteId
WHERE
    MT.[Name] = 'Bob';

db<>fiddle online demo