problem with GROUP BY CLAUSE in ACCESS

i need your help in access db

开发者_如何学JAVA i have a table like this

ID     CLASS    LESSON
1      7        MATH
2      7        CHEM
3      8        GEOM
4      8        MATH
5      8        CHEM
6      9        MATH
...

in mysql sql command like this

select CLASS, LESSON
from t_class
group by LESSON

returns result like

ID     CLASS     LESSON
1      7         MATH
2      7         CHEM
3      8         GEOM
...

but access sql command like this

select CLASS, LESSON
from t_class
group by LESSON

gives error

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

So what is the problem and how to solve the problem..

Thanks

The problem is, that you need to specify CLASS in an aggregate function, like sum, max, min, avg or the likes.

You could try:

select sum(CLASS), LESSON
from t_class
group by LESSON

You could also do

select CLASS, LESSON
from t_class
group by LESSON, CLASS

but what's the point there?

This gives an error because if you use aggregate functions you can only get fields you GROUP BY-ed for, and the result of aggregate functions.

It also not clear what value of CLASS you want to get? The least possible value? Then use an aggregate function MIN like:

select MIN(CLASS), LESSON
from t_class
group by LESSON

you need to use Aggregate function on LESSON or CLASS (as per your requirement), since you're using GROUP BY