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

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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

Method 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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply