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


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


>Solution :

Try this

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

Leave a ReplyCancel reply