Sleepyfalcon Sleepyfalcon - 2 years ago 147
Java Question

UCanAccess Group By

Need to learn a how work with UCanAccess where I can use a Group By with these selects.

String sql = "SELECT Route_To, BatchNo, BatchScOpr, BatchTrack, Time_Stamp,
sum(fix_bat_sum.STOTS + fix_bat_sum.STOTR) AS Sum
FROM fix_bat_sum GROUP BY fix_bat_sum.BatchNo";

I used this with a MySQL DB and it works fine but when I try to do it with a .mdb UCanAccess throws null pointer errors like this

java.sql.SQLSyntaxErrorException: expression not in aggregate or GROUP BY columns: PUBLIC.FIX_BAT_SUM.ROUTE_TO

Would love some help guys

Answer Source

You have to list all the non-aggregated columns in the GROUP BY clause.

GROUP BY Route_To, BatchNo, BatchScOpr, BatchTrack, Time_Stamp

MySQL is more permissive about this than other databases, although the default changed in MySQL 5.7.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download