aggregate function error

[TommyT]

You tried to execute a query that does not include the specified
function as part of an aggregate function

SELECT Sum([amt]) AS Expr1, Testsales.orderid, Testsales.product,
Testsales.amt
FROM Testsales
WHERE (((Testsales.product)="widget"));

Any one know why this generates an error?

 

[Rick Brandt]

You tried to execute a query that does not include the specified
function as part of an aggregate function

SELECT Sum([amt]) AS Expr1, Testsales.orderid, Testsales.product,
Testsales.amt
FROM Testsales
WHERE (((Testsales.product)="widget"));

Any one know why this generates an error?

In a Totals query all fields have to be either aggregated (sum, min, max, etc.),
or have Group By on them. You have three fields in that query with neither of
these.

It also makes no sense to include both amt and sum(amt) in the same query. The
aggregation (in this case sum) needs to be PER-SOME-FIELD or combination of
fields. Do you want the total amt for the order? If so...

SELECT Testsales.orderid, Sum([amt]) AS Expr1
FROM Testsales
WHERE Testsales.product = "widget"
GROUP BY Testsales.orderid

 

[TommyT]

Thanks,

I think that will work!

You tried to execute a query that does not include the specified
function as part of an aggregate function

SELECT Sum([amt]) AS Expr1, Testsales.orderid, Testsales.product,
Testsales.amt
FROM Testsales
WHERE (((Testsales.product)="widget"));

Any one know why this generates an error?

In a Totals query all fields have to be either aggregated (sum, min, max, etc.),
or have Group By on them. You have three fields in that query with neither of
these.

It also makes no sense to include both amt and sum(amt) in the same query. The
aggregation (in this case sum) needs to be PER-SOME-FIELD or combination of
fields. Do you want the total amt for the order? If so...

SELECT Testsales.orderid, Sum([amt]) AS Expr1
FROM Testsales
WHERE Testsales.product = "widget"
GROUP BY Testsales.orderid