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