SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE MyTable (
  "ID" INTEGER,
  "Name" VARCHAR(3),
  "RouteId" INTEGER
);

INSERT INTO MyTable
  ("ID", "Name", "RouteId")
VALUES
  ('1', 'Bob', '1001'),
  ('2', 'Bob', '1002'),
  ('3', 'Ana', '1001'),
  ('4', 'Jim', '1001'),
  ('5', 'Eli', '1001');
5 rows affected

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';


ID
Name
RouteId
Total


1
Bob
1001
4

2
Bob
1002
1


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';


ID
Name
RouteId
Total


1
Bob
1001
4

2
Bob
1002
1


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';


ID
Name
RouteId
Total


1
Bob
1001
4

2
Bob
1002
1