Aggregate Function?

[mvpkris]

I'm trying to set up an equation to multiply unit prices with quantities of multiple units creating a total. I believe the equation is put in to place correctly, however when I try to execute it, i revieved this message:

"You tried to execute a query that does not include the specified expression '[000DISTRIBUTORS_MAILTO_BILLTO].[QUANTITY1]*[000DISTRIBUTORS_MAILTO_BILLTO].[UNIT PRICE1]+[000DISTRIBUTORS_MAILTO_BILLTO].[QUANTITY2]*[000DISTRIBUTORS_MAILTO_BILLTO].[UNIT PRICE2]+[000DISTRIBUTORS_MAILTO_BILLTO].[QUANTITY3]*[000DISTRIBUTORS_MAILTO_BILLTO].[UNIT PRICE3]' as part of an aggregate function."

The equation continues to 7 allong with a Shipping Cost. I checked the spelling of everything and it is correct. Any ideas?

 

[PHV]

Any expression in the SELECT list that is not involved in an aggreate function (Sum, Count, Max, ...) should be in the GROUP BY clause.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mvpkris]

Thanks for the fast response, however I do have an expression "AS TOTAL" at the end of the equation.

[000DISTRIBUTORS_MAILTO_BILLTO].[UNIT PRICE7]+
[000DISTRIBUTORS_MAILTO_BILLTO].[SHIPPING] AS TOTAL,

 

[lespaul]

can you post the entire SQL?

Leslie

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

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[mvpkris]

Sure, here it is, sorry it's not cut properly::

SELECT
[000DISTRIBUTORS_MAILTO_BILLTO].BILL_TO,
[000DISTRIBUTORS_MAILTO_BILLTO].[INVOICE #],
[000DISTRIBUTORS_MAILTO_BILLTO].[PO #],
DISTRIBUTOR_REPORT_INDEX.[Due Date],
[000DISTRIBUTORS_MAILTO_BILLTO].[QUANTITY1]*
[000DISTRIBUTORS_MAILTO_BILLTO].[UNIT PRICE1]+
[000DISTRIBUTORS_MAILTO_BILLTO].[QUANTITY2]*
[000DISTRIBUTORS_MAILTO_BILLTO].[UNIT PRICE2]+
[000DISTRIBUTORS_MAILTO_BILLTO].[QUANTITY3]*
[000DISTRIBUTORS_MAILTO_BILLTO].[UNIT PRICE3]+
[000DISTRIBUTORS_MAILTO_BILLTO].[QUANTITY4]*
[000DISTRIBUTORS_MAILTO_BILLTO].[UNIT PRICE4]+
[000DISTRIBUTORS_MAILTO_BILLTO].[QUANTITY5]*
[000DISTRIBUTORS_MAILTO_BILLTO].[UNIT PRICE5]+
[000DISTRIBUTORS_MAILTO_BILLTO].[QUANTITY6]*
[000DISTRIBUTORS_MAILTO_BILLTO].[UNIT PRICE6]+
[000DISTRIBUTORS_MAILTO_BILLTO].[QUANTITY7]*
[000DISTRIBUTORS_MAILTO_BILLTO].[UNIT PRICE7]+
[000DISTRIBUTORS_MAILTO_BILLTO].[SHIPPING] AS TOTAL,
Customers.PHONE,
[000DISTRIBUTORS_MAILTO_BILLTO].PAID,
[000DISTRIBUTORS_MAILTO_BILLTO].EXCEPTIONS,
[000DISTRIBUTORS_MAILTO_BILLTO].PAID

FROM (Customers INNER JOIN DISTRIBUTOR_REPORT_INDEX ON Customers.COMPANY = DISTRIBUTOR_REPORT_INDEX.COMPANY) INNER JOIN [000DISTRIBUTORS_MAILTO_BILLTO] ON DISTRIBUTOR_REPORT_INDEX.[INVOICE #] = [000DISTRIBUTORS_MAILTO_BILLTO].[INVOICE #]

GROUP BY [000DISTRIBUTORS_MAILTO_BILLTO].BILL_TO,
[000DISTRIBUTORS_MAILTO_BILLTO].[INVOICE #],
[000DISTRIBUTORS_MAILTO_BILLTO].[PO #],
[DISTRIBUTOR_REPORT_INDEX].[Due Date],
[Customers].[PHONE],
[000DISTRIBUTORS_MAILTO_BILLTO].[PAID],
[000DISTRIBUTORS_MAILTO_BILLTO].[EXCEPTIONS],
[000DISTRIBUTORS_MAILTO_BILLTO].[QUANTITY1],
[000DISTRIBUTORS_MAILTO_BILLTO].[UNIT PRICE1],
[000DISTRIBUTORS_MAILTO_BILLTO].[QUANTITY2],
[000 DISTRIBUTORS_MAILTO_BILLTO].[UNIT PRICE2],
[000DISTRIBUTORS_MAILTO_BILLTO].[QUANTITY3],
[000DISTRIBUTORS_MAILTO_BILLTO].[UNIT PRICE3],
[000DISTRIBUTORS_MAILTO_BILLTO].[QUANTITY4],
[000DISTRIBUTORS_MAILTO_BILLTO].[UNIT PRICE4],
[000DISTRIBUTORS_MAILTO_BILLTO].[QUANTITY5],
[000DISTRIBUTORS_MAILTO_BILLTO].[UNIT PRICE5],
[000DISTRIBUTORS_MAILTO_BILLTO].[QUANTITY6],
[000DISTRIBUTORS_MAILTO_BILLTO].[UNIT PRICE6],
[000DISTRIBUTORS_MAILTO_BILLTO].[QUANTITY7],
[000DISTRIBUTORS_MAILTO_BILLTO].[UNIT PRICE7],
[000DISTRIBUTORS_MAILTO_BILLTO].[SHIPPING]

HAVING ((([000DISTRIBUTORS_MAILTO_BILLTO].[PAID])=No))
ORDER BY [DISTRIBUTOR_REPORT_INDEX].[Due Date];

 

[PHV]

As you don't use any aggregate function, simply get rid of the whole GROUP BY clause and replace HAVING with WHERE.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mvpkris]

That worked, thank you!

Sorry, but one more question (apparently I am not good at SQL...) it is now giving me this:

"Syntax error (missing operator) in query expression 'DISTRIBUTOR_REPORT_INDEX.[INVOICE #] = [000DISTRIBUTORS_MAILTO_BILLTO].[INVOICE #]

[000DISTRIBUTORS_MAILTO_BILLTO].BILL_TO"

And highlights the bolded portions below...

SELECT
[000DISTRIBUTORS_MAILTO_BILLTO].BILL_TO,
[000DISTRIBUTORS_MAILTO_BILLTO].[IN<b>VOICE #],
[000DISTRIBUTORS_MA</b>ILTO_BILLTO].[PO #],

 

[PHV]

I said the WHOLE group by clause, ie from GROUP (inclusive) to HAVING (exclusive) ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886