Update records based on Count

[RJL1]

Hi folks,

I got this issue where I need to flag certain records so I can process them differently (or not) based on the count of the number of items per line number. My data looks like this.

ERP_Line_Num   Item        Count   
001            0083104       
001            0210901      
002            0049703       
003            0049806

I need to update each record with the count (distinct number of items) for each ERP Line Num. My end data needs to look like this. So Each record for ERP Line one would have a 2 since there are 2 items than have ERP Line Num = 1

ERP_Line_Num   Item         Count  
001            0083104        2
001            0210901        2
002            0049703        1
003            0049806        1

Any assistance with this is greatly appreciated
Thanks
RJL

 

[sabinUE]

Hi,

;with ERP (ERP_Line_Num, Item)
AS
	(select '001','0083104'  union all
	select '001','0210901' union all
	select '002','0049703' union all
	select '003','0049806')

SELECT ERP_Line_Num
			, Item 
			,COUNT(ERP_Line_Num) OVER(PARTITION BY ERP_Line_Num ORDER BY ERP_Line_Num ASC) AS [Count]
FROM 
	ERP
GROUP BY 	ERP_Line_Num, Item

sabin MCP

 

[sabinUE]

OR

SELECT
	ERP.ERP_Line_Num
	,ERP.Item
	,A.[Count]
FROM
	ERP
	INNER JOIN 
	(SELECT
		ERP_Line_Num
		,COUNT(ERP_Line_Num) AS [Count]
		FROM ERP
		GROUP BY ERP_Line_Num) AS A
	ON ERP.ERP_Line_Num = A.ERP_Line_Num

sabin MCP

 

[RyanEK]

Hi,

How about...

declare @temp table (ERP_Line_Num varchar(3), Item varchar(10), Count int)
insert into @temp select '001','0083104',null      
insert into @temp select '001','0210901',null      
insert into @temp select '002','0049703',null      
insert into @temp select '003','0049806',null

update t1 set
t1.count = (select count(0) from @temp t2 where t2.ERP_Line_Num = t1.ERP_Line_Num)
from @temp t1

select * from @temp