Access SQL and GROUPBY

[bulrush]

A2003 on Winxp

While I am researching some of the problems I have posted in this forum about aggregate functions in SQL queries, I am noticing something. In regular SQL, it seems you do not need every field in the SELECT clause also go in the GROUPBY clause, but in Access, every field in the SELECT clause MUST also appear in the GROUPBY clauses if the field is not part of an aggregate function, like MAX().

Is this true of Access?

 

[namliam]

In regular SQL, it seems you do not need every field in the SELECT clause also go in the GROUPBY clause, but in Access, every field in the SELECT clause MUST also appear in the GROUPBY clauses if the field is not part of an aggregate function, like MAX().

Is this true of Access?

No .... welll yes... erm ...

ALL sql require atleast the visible fields in the SELECT to be in the group by bar the fields that are MAX/AVG/MIN/etc...
Some exceptions exist to complex MIN/MAX stuff where more fields may be needed and you can have more fields in the GROUP BY than in the Select I think, but minimum should be the fields in the SELECT

 

[jdraw]

When grouping, keep in mind that all columns that appear in your SELECT column list, that are not aggregated (used along with one of the SQL aggregate functions), have to appear in the GROUP BY clause too.


see http://www.sql-tutorial.com/sql-group-by-sql-tutorial/