TSQL Update & Group By error when Summing and multiplying.

Guys,
I have an issue on SQL Server with an update I am trying to peform on a table variable, the UDF is been called from a "parent" stored proc.

I have pasted in the bones of the SQL. I am trying to update a currency field in the table variable with the SUM(a * b) * c.
But the issue is with C, that it doesnt like it. Giving the error,

"Msg 8120, Level 16, State 1, Line 227
Column 'EstimateItem.Qty' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

The fields A & B are in a child table of the value C.

So you may have

Parent: EstimateItem
Item_ID     Rate_Code            Qty            
----------- -------------------- ------------------------------
338           D201101A             137.00          
339           D202102A             100.00          
340           D202103B             600.00          
341           D230101B             2000.00        


Child: Link between ITEM and RATEDETAILS (Needed for other processes in the system.)
Estimate_Rate_ID Rate_ID     Rate_Code       Item_ID    
---------------- ----------- -------------------- ----------- ---------------------------------------
641              491         D201101A            338          
642              499         D202102A            339            
643              502         D202103B                           340            
644              740         D230101B            341            
645              699         D221102D            342              


Child: TABLE EstimateItemRateDetails (With Figures for COST * QTY)
Estimate_Rate_    Estimate_     Item_Resource                    Qty         Cost      Rate           Rate_Details_ID
Details_ID              Rate_ID
------------------------ ---------------- -------------------------------------------------------------------------------------
1855         641                 Groundworks labourer            1.00        7.97       8.97           1303          
1856      641               Skip inc rental & tip fees            0.37       7.88       8.88           1304          
1857      641              JCB 3C                                        0.28       10.69     11.69          1305            
1858      641              Chainsaw                                   0.50        0.84        1.84          1306            
1859      642              JCB 3C                                       0.02       10.69      11.69          1330            
1860      643              JCB 3C                                       0.09       10.69      12.69          1333          
1861      644              Muck away lorry & tip fees       1.00        6.75       5.75            2014            
1862      644              JCB 3C                                      0.07       10.69      10.69          2015            
1863      645              Groundworks labourer              0.33        7.97       7.97           1885            
1864      645              MOT Type 1                              1.35        8.80       8.80           1886          
1865      645              JCB 3C                                      0.35       10.69      10.69         1887            
1866      645              Wacker Plate                            0.33       0.67        0.67          1888            
1867      641              General Labour                         1.00        7.97        7.97          1356      

DECLARE @Contract_ID int 
SET @Contract_ID= 30
 
 
DECLARE  @tempres table(
	Contract_ID [T_ENTITYPK ] NOT  NULL ,
	Cost_Element_ID [T_ENTITYPK]  NOT NULL ,
	Sub_Element_ID [T_ENTITYPK]  NULL ,
	Cost_Head_ID [T_ENTITYPK]   NOT NULL ,
	Cost_Budget [T_AMOUNT]  NULL  DEFAULT ((0.00)) ,
	Cost_Budget_Qty [T_AMOUNT]  NULL  DEFAULT ((0.00)) ,
	Revenue_Budget [T_AMOUNT]  NULL  DEFAULT ((0.00)) ,
	Revenue_Budget_Qty [T_AMOUNT]  NULL  DEFAULT (0.00)) ,
	Revised_Revenue_Budget [T_AMOUNT]  NULL  DEFAULT (0.00)) ,
	Revised_Cost_Budget [T_AMOUNT]  NULL  DEFAULT ((0.00)) ,
	Est_Item [T_AMOUNT]  NULL  DEFAULT ((0.00)) ,
	PRIMARY KEY (Contract_ID, Cost_Element_ID,Cost_Head_ID) 
	)
 
Insert into the table variable first, grouping on the fields specified, as each unique record in EstimateItemRateDetails will have an entry in the RESOURCE table, For example the  General Labour & Groundworks labourer will each have an entry in the RESOURCE table, but will have different rates etc, but will have the same COSTELEMENT, SUBELEMENT and COSTHEAD. So we can group these together.
					
INSERT INTO @tempres (Contract_ID , Cost_Element_ID , Sub_Element_ID, Cost_Head_ID)
 SELECT EstT.Contract_ID, Res.Cost_Element, EstH.Sub_Element_ID, Res.Cost_Head  FROM   Resources Res
	JOIN RateDetails RateDets on RateDets.Rate_Item = Res.Resource
	JOIN EstimateItemRateDetails EIRD ON EIRD.Rate_Details_ID = RateDets.Rate_Details_ID
	JOIN EstimateItemRates EstRate on EstRate.Estimate_Rate_ID = EIRD.Estimate_Rate_ID
	JOIN EstimateItem EstItem on EstItem.Item_ID = EstRate.Item_ID
	JOIN EstimateHeading EstH ON EstH.Heading_ID  = EstItem.Heading_ID
	JOIN EstimateTitle EstT ON EstT.Title_ID  = EstH.Title_ID
WHERE  (@OVERRIDELABOURANALYSIS = 'False' OR  EIRD.Type <> 'L'  ) 
 
GROUP BY EstT.Contract_ID, Res.Cost_Element, EstH.Sub_Element_ID, Res.Cost_Head  
 
 
--NOW UPDATE:
UPDATE @tempres
         SET tmp.Cost_Budget = tmp.Cost_Budget + 
               ISNULL((SELECT (SUM( EIRD.Qty * EIRD.Cost) * EstItem.Qty)
		FROM Resources Res
	JOIN RateDetails RateDets on RateDets.Rate_Item = Res.Resource
	JOIN EstimateItemRateDetails EIRD ON EIRD.Rate_Details_ID =   RateDets.Rate_Details_ID
	JOIN EstimateItemRates EstRate on EstRate.Estimate_Rate_ID = EIRD.Estimate_Rate_ID
	JOIN EstimateItem EstItem on EstItem.Item_ID = EstRate.Item_ID
	JOIN EstimateHeading EstH ON EstH.Heading_ID  = EstItem.Heading_ID
	JOIN EstimateTitle EstT ON EstT.Title_ID  = EstH.Title_ID
WHERE  (@OVERRIDELABOURANALYSIS = 'False' OR  EIRD.Type <> 'L'  ) 
       AND  tmp.Cost_Head_ID = Res.Cost_Head
       AND tmp.Cost_Element_ID =Res.Cost_Element
       AND tmp.Sub_Element_ID =  EstH.Sub_Element_ID
       AND tmp.Contract_ID =  EstT.Contract_ID), 0 )
FROM @tempres tmp

Select allOpen in new window