Too complex for me select statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Too complex for me select statement

Author

Topic

trackjunkie
Starting Member

31 Posts

Posted - 2014-01-09 : 11:53:21

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

5072 Posts

Posted - 2014-01-09 : 12:39:29

[code]
SELECT item, lot, sum(pieces) as pieces
FROM Flex_MachineActivityData
GROUP BY
item, lot;[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-10 : 07:41:07

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources