you tried to execute a query that does not include "" as part of the aggregate (1 Viewer)

[Rik_StHelens]

you tried to execute a query that does not include "" as part of the aggregate

Hi,

I have keep getting the error message "you tried to execute a query that does not include CUSTOMER as part of the aggregate function"

I've had a nose around the net but not found any solutions so thought i'd post.

Here is the SQL:

SELECT TRNSTK.CUSTOMER, TRNSTK.INVDATE, TRNSTK.STCODE, STOCK.SECTION, STOCK.PROFILE, STOCK.RIM, TRNSTK.SUMCODE, TRNSTK.MANUFCTR, TRNSTK.QUANTITY, VANRUN.DESCRIPN, Sum(IIf(TRNSTK.MANUFCTR="HA",1,0)) AS [No of Hankook], Sum(IIf(TRNSTK.MANUFCTR<>"HA",1,0)) AS [No of Non Hankook]

FROM (TRNSTK INNER JOIN VANRUN ON TRNSTK.VAN = VANRUN.NUMBER) INNER JOIN STOCK ON TRNSTK.STCODE = STOCK.STCODE

WHERE (((TRNSTK.CUSTOMER)="PRO002") AND ((TRNSTK.SUMCODE)="A" Or (TRNSTK.SUMCODE)="B")) OR (((TRNSTK.SUMCODE)="C" Or (TRNSTK.SUMCODE)="D")) OR (((TRNSTK.SUMCODE)="E" Or (TRNSTK.SUMCODE)="F")) OR (((TRNSTK.SUMCODE)="G" Or (TRNSTK.SUMCODE)="H")) OR (((TRNSTK.SUMCODE)="I" Or (TRNSTK.SUMCODE)="J")) OR (((TRNSTK.SUMCODE)="K")) OR (((TRNSTK.SUMCODE)="I"));


Thanks for your time.

 

[DCrake]

Re: you tried to execute a query that does not include "" as part of the aggregate

((TRNSTK.SUMCODE)="A" Or (TRNSTK.SUMCODE)="B")) OR (((TRNSTK.SUMCODE)="C" Or (TRNSTK.SUMCODE)="D")) OR (((TRNSTK.SUMCODE)="E" Or (TRNSTK.SUMCODE)="F")) OR (((TRNSTK.SUMCODE)="G" Or (TRNSTK.SUMCODE)="H")) OR (((TRNSTK.SUMCODE)="I" Or (TRNSTK.SUMCODE)="J")) OR (((TRNSTK.SUMCODE)="K")) OR (((TRNSTK.SUMCODE)="[B][COLOR="Red"]I[/COLOR][/B]"));

Can be truncated to In("A","B","C","D","E","F","G","H","I","J","K","L")

You also have I twice

 

[Rik_StHelens]

Re: you tried to execute a query that does not include "" as part of the aggregate

((TRNSTK.SUMCODE)="A" Or (TRNSTK.SUMCODE)="B")) OR (((TRNSTK.SUMCODE)="C" Or (TRNSTK.SUMCODE)="D")) OR (((TRNSTK.SUMCODE)="E" Or (TRNSTK.SUMCODE)="F")) OR (((TRNSTK.SUMCODE)="G" Or (TRNSTK.SUMCODE)="H")) OR (((TRNSTK.SUMCODE)="I" Or (TRNSTK.SUMCODE)="J")) OR (((TRNSTK.SUMCODE)="K")) OR (((TRNSTK.SUMCODE)="[B][COLOR=Red]I[/COLOR][/B]"));

Can be truncated to In("A","B","C","D","E","F","G","H","I","J","K","L")

You also have I twice

Yeah i noticed that just after i posted it. oops.

It would appear that if i remove the two count functions, the query runs.

Before i added in the count functions i tested the query, and it only pulled back results with PRO002 is the customer field, and A - K in the SUMCODE field.

Now when i remove the count functions, the query does run but pulls back all records, not just the ones that i specify.

Any ideas as to why?

 

[DCrake]

Re: you tried to execute a query that does not include "" as part of the aggregate

When running queries that have sums in them then other columns need to be identified as well. Such as Grouped By, First, Last, Expression, Where, Etc. Suggest you play around with the columns. Other than that create a new query based on this query.

David

 

[Brianwarnock]

Re: you tried to execute a query that does not include "" as part of the aggregate

Wow

THe bracketting looks suspect but using David's suggestion of IN(.....) will resolve that , however it is not just Customer that will be flagged as not being part of the aggregation but all of the fields except the Sum(iiif.... the Sum forces the query into a Totals query, you maybe able to justGroup by on the other fields. to try this select it as a totals query , you will need to select expression in the totals row for the sum(iifs

Brian

Edit see David came back on this whilst my old fingers were finding their slow way round the keyboard.

 

[Rik_StHelens]

Re: you tried to execute a query that does not include "" as part of the aggregate

Ahh ok,

i have got it working now, so thank you very much for your help.

However, my solution is supposed to also calculate a percentage based on a couple of values, in order to calculate a service failure rate.

I have the formulae giving me the answer i expect, but without the decimal point in the right place.

% Failure Rate: (100/[Total Jobs])*[No of Non Hankook]

at present Total Jobs = 947
No of Non Hankook = 429
Failure Rate = 4530.1%

obvioulsy it should be 45.301% but it wont put the decimal point where i am expecting.

Its probably a simple solution but i cannot for the life of me find it.

Thanks again

 

[DCrake]

Re: you tried to execute a query that does not include "" as part of the aggregate

You need to express the value as a percentage. Click on properties of the column and select format Percentage.
If that does not work add /100 onto the ned of your formula.

David

 

[Rik_StHelens]

Re: you tried to execute a query that does not include "" as part of the aggregate

You need to express the value as a percentage. Click on properties of the column and select format Percentage.
If that does not work add /100 onto the ned of your formula.

David

Thanks David

It was already expressed as a percentage, however i didn't think about dividing by 100 at the end. Like i said, simple solution

Thank you