SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Too complex for me select statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

trackjunkie
Starting Member

31 Posts

Posted - 01/09/2014 :  11:53:21  Show Profile  Reply with Quote
I have a table with the structure:

ITEM LOT PIECES
123 686 987
123 686 1000
123 797 500
321 808 333
321 808 555

I want to select into a temporary table (to join with other table later) the sum of the pieces for each distinct ITEM LOT combination. So the result for above would be:

ITEM LOT PIECES
123 686 1987
123 797 500
321 808 888

My thinking so far has been to create the TempTable with all three columns, then insert the item/lot combos with a select disctint statement. Then I tried to update the pieces column by selecting the SUM of the pieces for a given lot number. I have:

CREATE TABLE #TempList(
Item nvarchar(20),
Lot nvarchar(15),
TotalPieces Decimal(9,1)
)

INSERT INTO #TempList

SELECT DISTINCT [Item Number], [Lot Number] FROM Flex_MachineActivityData

Update #TempList
set TotalPieces = (Select sum(pcs)
from Flex_MachineActivityData
where Flex_MachineActivityData.[Lot Number] = #TempList.Lot)
From #TempList

Select * from #TempList

It compiles fine, then at run-time I get:
"Column name or number of supplied values does not match table definition"

Which makes me think the third column was not established. Ideas?

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 01/09/2014 :  12:39:29  Show Profile  Visit russell's Homepage  Reply with Quote

SELECT	item, lot, sum(pieces) as pieces
FROM	Flex_MachineActivityData
GROUP BY
	item, lot;
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/10/2014 :  07:41:07  Show Profile  Reply with Quote
you can create table itself using last posted query
ie like

SELECT	item, lot, sum(pieces) as pieces INTO #YourTempTable
FROM	Flex_MachineActivityData
GROUP BY
	item, lot;


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000