5

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?

2 Answers 2

9

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

0
6

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.