sql server : count records

I have a tableA (ID int, Match varchar, tot int)

ID Match Tot
1  123
2  123
3  12
4  12
5  4
6  12
7  8

Now, I want to calculate Tot which is total number of match exists in the table. for example 123 occured twice, 12 exist thrice and 开发者_如何转开发so on. Also note that I want the count only at first match. here is the expected result.:

ID Match Tot
1  123    2
2  123
3  12     3
4  12
5  4      1
6  12
7  8      1

Another case:

    ID Match Count Tot
    1  123   2
    2  123   1
    3  12    10
    4  12    10
    5  4     3
    6  12    5
    7  8     7

Now I want to add the count for the same match. expected result:

    ID Match Count Tot
    1  123   2      3
    2  123   1
    3  12    10     25
    4  12    10
    5  4     3       3
    6  12    5  
    7  8     7       7

Thanks

---

WITH tableA(ID, Match) AS
(
SELECT 1,123 UNION ALL
SELECT 2,123 UNION ALL
SELECT 3,12 UNION ALL
SELECT 4,12 UNION ALL
SELECT 5,4 UNION ALL
SELECT 6,12 UNION ALL
SELECT 7,8
)
SELECT *,
       CASE
         WHEN ROW_NUMBER() OVER (PARTITION BY Match ORDER BY ID) = 1 
         THEN COUNT(*) OVER (PARTITION BY Match)
       END AS Tot
FROM   tableA
ORDER  BY ID  

---

SELECT match, COUNT(match ) as Tot
FROM tableA
GROUP BY match

---

Solution 1:

DECLARE @MyTable TABLE
(
    ID INT PRIMARY KEY
    ,Match VARCHAR(10) NOT NULL
    ,Tot INT NULL
);
INSERT  @MyTable(ID, Match)
SELECT 1,  123
UNION ALL
SELECT 2,  123
UNION ALL
SELECT 3,  12
UNION ALL
SELECT 4,  12
UNION ALL
SELECT 5,  4
UNION ALL
SELECT 6,  12
UNION ALL
SELECT 7,  8;

--SELECT
SELECT  *
        ,CASE 
            WHEN ROW_NUMBER()OVER(PARTITION BY a.Match ORDER BY a.ID ASC)=1
            THEN COUNT(*)OVER(PARTITION BY a.Match)
        END TotCalculated
FROM    @MyTable a;

--UPDATE
WITH MyCTE
AS
(
    SELECT  a.Tot
            ,CASE 
                WHEN ROW_NUMBER()OVER(PARTITION BY a.Match ORDER BY a.ID ASC)=1
                THEN COUNT(*)OVER(PARTITION BY a.Match)
            END TotCalculated
    FROM    @MyTable a
)
UPDATE  MyCTE 
SET     Tot = TotCalculated;

SELECT  *
FROM    @MyTable;

Solution 2:

UPDATE  @MyTable
SET     Tot = NULL;

SELECT  x.ID, y.Num
FROM
(
    SELECT  b.Match, MIN(b.ID) ID
    FROM    @MyTable b
    GROUP BY b.Match
) x INNER JOIN
(
    SELECT  a.Match, COUNT(*) AS Num
    FROM    @MyTable a
    GROUP BY a.Match
) y ON x.Match = y.Match
ORDER BY x.ID

UPDATE  @MyTable
SET     Tot = t.Num
FROM    @MyTable z 
INNER JOIN
(
        SELECT  x.ID, y.Num
        FROM
        (
            SELECT  b.Match, MIN(b.ID) ID
            FROM    @MyTable b
            GROUP BY b.Match
        ) x INNER JOIN
        (
            SELECT  a.Match, COUNT(*) AS Num
            FROM    @MyTable a
            GROUP BY a.Match
        ) y ON x.Match = y.Match
) t     ON z.ID = t.ID;

SELECT  *
FROM    @MyTable;