Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Update records based on Count

Status
Not open for further replies.

RJL1

Technical User
Oct 3, 2002
228
US
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.

Code:
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

Code:
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
 
Hi,

Code:
;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
 
OR
Code:
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
 
Hi,

How about...

Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top