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

Open in new window

CMSIRLAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

brejkCommented:
Why not include the third column into SUM?

... SELECT SUM( EIRD.Qty * EIRD.Cost * EstItem.Qty) FROM ...

If there is just a single EstItem.Qty for each item, then the result will be the same since:

(a * b + d * e) * c = (a * b * c + d * e * c)

Otherwise this statement is not proper and you have to include EstItem.Qty in GROUP BY clause (which will probably cause that you lose business logic here) or you need to use a subquery to provide single EstItem.Qty for each item.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please try this update syntax:

UPDATE tmp
SET tmp.Cost_Budget = tmp.Cost_Budget 
+ 
               ISNULL(
   (SELECT x.sum_cost * EstItem.Qty
      FROM ( SELECT SUM( EIRD.Qty * EIRD.Cost) sum_cost
	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
      WHERE  (@OVERRIDELABOURANALYSIS = 'False' OR  EIRD.Type <> 'L'  ) 
         AND tmp.Cost_Head_ID = Res.Cost_Head
         AND tmp.Cost_Element_ID =Res.Cost_Element
        )x
	JOIN EstimateItem EstItem on EstItem.Item_ID = x.Item_ID
	JOIN EstimateHeading EstH ON EstH.Heading_ID  = EstItem.Heading_ID
	JOIN EstimateTitle EstT ON EstT.Title_ID  = EstH.Title_ID
     WHERE tmp.Sub_Element_ID =  EstH.Sub_Element_ID
       AND tmp.Contract_ID =  EstT.Contract_ID
 
  ), 0 )
FROM @tempres tmp

Open in new window

0
CMSIRLAuthor Commented:
Oh god I cannot believe I did not spot that, thanks very much, maybe I am just gone google eyed and never spotted the easiest way of doing it!! *hang my head in shame* LOL, thanks again.
0
CMSIRLAuthor Commented:
angelll, thanks for the answer, I took the first one there as the answer if that is ok.

Thank again
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.