Database Administration
sql-server count where
Updated Sun, 17 Jul 2022 06:43:39 GMT

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


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



Solution

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







External Links

External links referenced by this document: