 |
| SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005. |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|
|
November 18th, 2003, 03:01 PM
|
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 149
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Expression causes an Aggregate error
The following MS SQL2000 code generates this error:
Quote:
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:
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:
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??
Code:
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));
__________________
Mitch
|
|
November 18th, 2003, 03:41 PM
|
 |
Friend of Wrox
|
|
Join Date: Aug 2003
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
|
|
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.
|
|
November 18th, 2003, 03:59 PM
|
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 149
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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.
|
|
November 18th, 2003, 04:20 PM
|
|
Friend of Wrox
|
|
Join Date: Aug 2003
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
|
|
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.
|
|
|
