sql query for counting the records of particular id and show in column-CodePudding

I have a following table:-

declare @tab table(name varchar(10),id int)

insert into @tab values ('A',1),('B',1),('C',1),('D',1),('E',2),('F',2)

I need following output:-

declare @tab1 table(name varchar(10),id int, cnt int)

insert into @tab1 values ('A',1,4),('B',1,4),('C',1,4),('D',1,4),('E',2,2),('F',2,2)
select * from @tab1

I tried following query:-

select name,id,count(*) as cnt
from @tab 
group by name,id

Thanks

CodePudding user response：

Try this

select name
     , id
     , count(*) over(partition by id) as cnt
from @tab
;

CodePudding user response：

Done

;with a as
(
select Id,name, count(*) over (partition by id) cnt

from @tab
)
select Id,name,cnt
from a