|
Subject:
|
Expression causes an Aggregate error
|
|
Posted By:
|
Mitch
|
Post Date:
|
11/18/2003 2:01:17 PM
|
The following MS SQL2000 code generates this error:
quote:
Column 'vProcMgrLineSpeedPerWOID_Rev.AcualParameterValue' is invalid in the select list
because it is not contained in either an aggregate function or the GROUP BY clause.
If I remove the following line it works:
quote:
(((CONVERT(float,[AcualParameterValue])*(Avg([LSYieldMult])-((Sum([QtyGood])/Sum([TotalRun]))*[ComboLengthActual]))/(CONVERT(float,[AcualParameterValue]))*100))) AS Diff
If I leave it in and put it in the Group By then I get the error:
quote:
Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.
So what do I do with it??
SELECT [T_SetupSheetHistoryProduction].WONum,
[T_SetupSheetHistoryProduction].WOIDSub,
[T_SetupSheetHistoryProduction].Revision,
[T_SetupSheetHistoryProduction].ComboCustPartNum AS Item,
[T_SetupSheetHistoryCombinationsDetail].ComboLengthActual AS Length,
Sum([T_SetupSheetHistoryProduction].QtyGood) AS TotalGoodQty,
Sum([T_SetupSheetHistoryProduction].TotalRun) AS RunMins,
CONVERT(float,[AcualParameterValue]) AS LineSpdRprt,
CONVERT(float,[AcualParameterValue])*([LSYieldMult]) AS RepYld,
Sum([QtyGood])/Sum([TotalRun])*[ComboLengthActual] AS CalcLineSpd,
(((CONVERT(float,[AcualParameterValue])*(Avg([LSYieldMult])-((Sum([QtyGood])/Sum([TotalRun]))*[ComboLengthActual]))/(CONVERT(float,[AcualParameterValue]))*100))) AS Diff
FROM (([T_SetupSheetHistoryProduction]
INNER JOIN [T_SetupSheetHistoryCombinationsDetail]
ON ([T_SetupSheetHistoryProduction].ComboCustPartNum = [T_SetupSheetHistoryCombinationsDetail].ComboCustPartNum)
AND ([T_SetupSheetHistoryProduction].ECN = [T_SetupSheetHistoryCombinationsDetail].ECN)
AND ([T_SetupSheetHistoryProduction].WONum = [T_SetupSheetHistoryCombinationsDetail].WOID)
AND ([T_SetupSheetHistoryProduction].QuoteID = [T_SetupSheetHistoryCombinationsDetail].QuoteID))
INNER JOIN vProcMgrLineSpeedPerWOID_Rev
ON ([T_SetupSheetHistoryProduction].Revision = vProcMgrLineSpeedPerWOID_Rev.Revision)
AND ([T_SetupSheetHistoryProduction].ECN = vProcMgrLineSpeedPerWOID_Rev.ECN)
AND ([T_SetupSheetHistoryProduction].QuoteID = vProcMgrLineSpeedPerWOID_Rev.QuoteID)
AND ([T_SetupSheetHistoryProduction].WONum = vProcMgrLineSpeedPerWOID_Rev.WOID))
INNER JOIN [T_SetupSheetHistoryHeader]
ON ([T_SetupSheetHistoryProduction].Revision = [T_SetupSheetHistoryHeader].Revision)
AND ([T_SetupSheetHistoryProduction].ECN = [T_SetupSheetHistoryHeader].ECN)
AND ([T_SetupSheetHistoryProduction].WOIDSub = [T_SetupSheetHistoryHeader].WOIDSub)
AND ([T_SetupSheetHistoryProduction].WONum = [T_SetupSheetHistoryHeader].WOID)
AND ([T_SetupSheetHistoryProduction].QuoteID = [T_SetupSheetHistoryHeader].QuoteID)
WHERE (((vProcMgrLineSpeedPerWOID_Rev.AcualParameterValue) Is Not Null))
GROUP BY [T_SetupSheetHistoryProduction].WONum,
[T_SetupSheetHistoryProduction].WOIDSub,
[T_SetupSheetHistoryProduction].Revision,
[T_SetupSheetHistoryProduction].ComboCustPartNum,
[T_SetupSheetHistoryCombinationsDetail].ComboLengthActual,
CONVERT(float,[AcualParameterValue]),
CONVERT(float,[AcualParameterValue])*([LSYieldMult])
HAVING ((([T_SetupSheetHistoryProduction].WONum)='13808')
AND ((Sum([T_SetupSheetHistoryProduction].QtyGood))>0
AND (Sum([T_SetupSheetHistoryProduction].QtyGood)) Is Not Null)
AND ((Sum([T_SetupSheetHistoryProduction].TotalRun))>0
AND (Sum([T_SetupSheetHistoryProduction].TotalRun)) Is Not Null));
|
|
Reply By:
|
planoie
|
Reply Date:
|
11/18/2003 2:41:02 PM
|
Even though you have aggregation functions in that part of the select, the outer part is not an aggregate function so I think that's why SQL doesn't like it.
This is probably a bit of a hack, but assuming that that calculation works for you, it would return the same value for each row in the group (based on the other grouped columns) so maybe you could wrap that whole calculation inside of MAX. If all the values are the same, then MAX wouldn't give you anything erroneous. Not sure if that would work however.
another suggestion: look into aliases for your tables. Example...
SELECT t1.field1, t2.field1
FROM thisIsAReallyLongTableNameThatIDontWantToRetype AS t1
Peter
------------------------------------------------------
Work smarter, not harder.
|
|
Reply By:
|
Mitch
|
Reply Date:
|
11/18/2003 2:59:43 PM
|
I am not sure what you mean by "wrap the whole calculation."
I tried:
MAX((((CONVERT(float,[AcualParameterValue])*(Avg([LSYieldMult])-((Sum([QtyGood])/Sum([TotalRun]))*[ComboLengthActual]))/(CONVERT(float,[AcualParameterValue]))*100)))) AS Diff
But it gave me an error saying that I couldn't use max with aggregates inside it.
|
|
Reply By:
|
planoie
|
Reply Date:
|
11/18/2003 3:20:45 PM
|
Yeah. that's what I meant, and shucks, that error doesn't surprise me.
I wonder: can you put each of your aggregate functions into separate columns, then have a calculated column that is the finale result? Is SQL smart enough to figure that out? I don't do that much of this type of SQL. Sorry I can't be of more help.
Peter
------------------------------------------------------
Work smarter, not harder.
|
