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.
| 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
|
|
|
|
|
|
|
Too complex for me select statement