Not Part of aggregate function error

[jadams0173]

I'm trying to combine two sql statements into one and keep getting a error that says "you tried to execute a query that does not include the specific expression '(Count([SFCNumber])-TBL_ALL_PACK_PLANS.FridayCommit)*100' as part of an aggregate function. I'm having trouble finding the cause of this. The combined statement is:

SELECT TBL_ALL_PACK_PLANS.F1, TBL_ALL_PACK_PLANS.FRIDAYCOMMIT, Count(qry_NewDailyWip.SFCNUMBER) AS SFCsInWip, (Count([SFCNUMBER])-[FRIDAYCommit])*[ATTR] AS NeedToWave, IIf(IsNull([waveprocess]),'NEEDS REVIEW',[waveprocess]) AS Wave, (Abs([BOM_WC_TOOL_WAVE_ONLY]![WCT_SEC/BRD]*(Count([SFCNUMBER])-[FRIDAYCommit])*[ATTR])) AS [WaveCycleTime(SEC/BRD)], IIf(IsNull([MaxofQuantity]),0,([MaxOfQUANTITY])*3600) AS [GateCycleTime_(SEC/CRD)], IIf(Count([SFCNUMBER])>=[FRIDAYCommit],'N','Y') AS YN, (Abs([WaveCycleTime(SEC/BRD)]))/3600 AS RunTime, IIf(IsNull([YIELD]),85,[YIELD]) AS NEWYIELD, ((100-[NEWYIELD])/100)+1 AS ATTR, BOM_WC_TOOL_WAVE_ONLY.[WCT_SEC/BRD], BOM_WC_TOOL_WAVE_ONLY.Letter  INTO FRIDAYSCHEDULECH501269 FROM (((TBL_ALL_PACK_PLANS LEFT JOIN qry_NewDailyWip ON TBL_ALL_PACK_PLANS.F1 = qry_NewDailyWip.F1) LEFT JOIN QRY_Gating_operation AS QRY_Gating_operation_1 ON TBL_ALL_PACK_PLANS.F1 = QRY_Gating_operation_1.F1) LEFT JOIN BOM_WC_TOOL_WAVE_ONLY ON TBL_ALL_PACK_PLANS.F1 = BOM_WC_TOOL_WAVE_ONLY.PARENT) LEFT JOIN Q
RY_UNIQUE_YIELD_WITH_REV ON TBL_ALL_PACK_PLANS.F1 = QRY_UNIQUE_YIELD_WITH_REV.ITEM  GROUP BY TBL_ALL_PACK_PLANS.F1, TBL_ALL_PACK_PLANS.FRIDAYCOMMIT, IIf(IsNull([waveprocess]),'NEEDS REVIEW',[waveprocess]), IIf(IsNull([MaxofQuantity]),0,([MaxOfQUANTITY])*3600), IIf(IsNull([YIELD]),85,[YIELD]), BOM_WC_TOOL_WAVE_ONLY.[WCT_SEC/BRD], BOM_WC_TOOL_WAVE_ONLY.Letter  HAVING ((TBL_ALL_PACK_PLANS.FRIDAYCOMMIT)>0) AND Count([SFCNUMBER]) < [FRIDAYCommit] ORDER BY(((Count([SFCNUMBER]) - [FRIDAYCommit])) * (((100 - [NEWESTYIELD]) / 100) + 1)) * (IIf(IsNull([maxofQUANTITY]), 0, ([maxofQUANTITY]) * 3600))

I think the problem lies in the ORDER BY part, but I can't seem to make it right.
Any help would be greatly welcomed. Also any advice on how to approach resolving issues like this in long sql statements.

 

[lespaul]

I'm not going to try and decipher all that, but here's what is going on. When you use an aggregate function (like COUNT, SUM, AVG) all the fields in your SELECT list have to be included in your group by list:

SELECT Field1, Field2, Count(*)
From SomeTable
Group By Field1, Field2



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[jadams0173]

Thanks for the reply lespaul for giving me a specific area to look. I hated posting that statement. I do know that if I remove the ORDER BY it works. I'll keep searching and thanks again for the advice.