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:
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:
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?