CODEWITHSUNDEEP
sql-server
mql4beginner
mql4beginner

Reputation: 2233

How to to add a new column that will count the number of duplicates value from a specific column

I would like to add a new column that will count the number of duplicates value from a specific column and present the count outcome in the first occurrence row of the table. For example, having the toy table below :

ID LOGIN NAME   COUNTRY
11 a@com  Aron  USA
12 a@com  Aron  Israel
13 ff@com Lucci Germany

I would like to get the count of LOGIN field in the first occurrence row of the table:

ID LOGIN NAME   COUNTRY Count_LOGIN
11 a@com  Aron  USA         2
12 a@com  Aron  Israel      
13 ff@com Lucci Germany     1

How can I do it? I know that I can save the table as a temp and then do something like what is written below but it is not exactly what I need.

SELECT LOGIN , COUNT(*) TotalCount
FROM #tt -- #tt is the first table 
GROUP BY LOGIN 
ORDER BY COUNT(*) DESC

Upvotes: 3

Views: 4384

Answers (3)

Kilren
Kilren

Reputation: 415

you can try windowed function to get the count and to decide when to show result.

SELECT
    Id
  , Login
  , Name
  , Country
  , COUNT(*) OVER (PARTITION BY Login) as Cnt
  , CASE WHEN 1 = ROW_NUMBER() OVER (PARTITION BY Login ORDER BY Id) -- (Order by need to match query Order statement)
         THEN COUNT(*) OVER (PARTITION BY Login) as Cnt
         ELSE NULL
    END AS Cnt_ONLY_ON_FIRST_RECORD
FROM #tt
ORDER BY 
    ID -- (Order by need to match Order in CASE condition statement)

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

First, you can use ALTER TABLE to add the new column Count_LOGIN. Then, use an UPDATE statement with a subquery to populate that column. The query in your question looks spot on for what you want.

ALTER TABLE #tt
ADD Count_LOGIN int;

UPDATE #tt
SET Count_LOGIN =
(
    SELECT COUNT(*)
    FROM #tt b
    WHERE #tt.LOGIN = b.LOGIN
    GROUP BY LOGIN
)

Update:

Assuming you only want to update the record for each LOGIN group which has the smallest ID value, you can try the following join UPDATE query:

UPDATE #tt
SET #tt.Count_LOGIN = t.TotalCount
FROM #tt
INNER JOIN
(
    SELECT LOGIN, MIN(ID) AS ID, COUNT(*) AS TotalCount
    FROM #tt
    GROUP BY LOGIN
) t
    ON #tt.ID = t.ID AND #tt.LOGIN = t.LOGIN

This query will only affect records from your original #tt table which match the minimum ID value for each LOGIN group. This would leave the other records in that group without a value.

Upvotes: 4

TheGameiswar
TheGameiswar

Reputation: 28900

create table #temp
(
id int,
loginn varchar(10),
name varchar(10),
cntry varchar(40)
)

insert into #temp
select 
11, 'a@com',  'Aron', 'USA'     union all
select 12, 'a@com',  'Aron',  'Israel'  union all
select 13, 'ff@com', 'Lucci', 'Germany'


;with cte
as
(
select *,ROW_NUMBER() over (partition by loginn order by id) as rn
 from #temp
 )
 select 
 id,loginn,name,cntry,
 case 
 when rn=1 then (select count(*) from cte  t1 where t1.loginn=t2.loginn) 
 else 0 
 end as duplicate
 from
cte t2

Output:

id  loginn  name    cntry   duplicate
11  a@com   Aron    USA         2
12  a@com   Aron    Israel      0
13  ff@com  Lucci   Germany     1

Upvotes: 2

Related Questions