CODEWITHSUNDEEP
javasqlms-access
Yousaf
Yousaf

Reputation: 29282

Expression not in aggregate or GROUP BY columns Exception

I have a table in MS-Access database named ItemRates which contains columns ItemID, ItemName, TotalFeet, RatePerItem, TotalRate and this database is connected with my java application.

When i run the following query

String sql = "SELECT SUM(TotalRate) AS ItemRateSum, TotalFeet FROM ItemRates";

I get the following exception

net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.7 expression not in aggregate or
GROUP BY columns: PUBLIC.ITEMRATES.TOTALFEET

I have seen other questions related to this exception on StackOverflow and one accepted answer suggested to add GroupBy clause at the end of the query. I added a GroupBy clause 

String sql = "SELECT SUM(TotalRate) AS ItemRateSum, TotalFeet FROM 
ItemRates GROUPBY ItemName";

and i got almost same exception (there's a slight difference between two exceptions at the end after the colon)

net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.7 expression not in aggregate or 
GROUP BY columns: GROUPBY.TOTALFEET

What am i doing wrong here ?

Upvotes: 0

Views: 4780

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

The correct syntax is:

SELECT SUM(TotalRate) AS ItemRateSum, TotalFeet
FROM ItemRates
GROUP BY TotalFeet;

That is, TotalFeet is not an argument to an aggregation function, so it needs to be in the GROUP BY.

Upvotes: 1

user7392562
user7392562

Reputation:

All columns which are selected and are not part of the aggregate functions have to be included in the group by clause

Upvotes: 0

Related Questions