Simple GROUP BY clause... error?

[ladygray]

I am sure there is a simple reason why I am getting errors. I have used GROUP BY statements before and not had this problem. So there must be something small that is wrong with my syntax that I can't figure out (I'm currently going through the SQL cookbook trying to find out what I've done wrong!)

Here is my script:
SELECT a.plot_numbe, a.age_of_tre, a.sample_yea, a.species_co, a.total_heig, b.species_ty FROM
all_tree_2008 a
INNER JOIN a3a_species b on a.species_co=b.species_co
WHERE a.age_of_tre IS NOT NULL AND a.total_heig > 12 AND b.species_ty='S'
ORDER BY sample_yea, plot_numbe;

I would like to add in a 'GROUP BY a.plot_numbe' in there somewhere but no matter where I put it in the script I get an error. Things like "statement ended properly" or "not a GROUP BY expression".

Any ideas would be greatly appreciated!

Thanks in advance,

Ladygray

[Rainbow River]

why you need GROUP BY? didn't see any reason to use GROUP BY clause.

[shamshe]

1)GROUP BY clause normally used with aggregate functions such as SUM or AVG
2) Each non-aggregated field must be listed in the GROUP BY clause
3) GROUP BY should placed before ORDER BY

[ladygray]

Hi Shamshe/ Rainbow River,

Thanks for your responses. I would like to group by plot number because the plot numbers are repeated because they are remeasured every five years. Shamshe, what did you mean by "non-aggregated field"?

Ladygray

[shamshe]

suppose you have:
SELECT f1,f2,f3, SUM(f4)
FROM table1;
and you'd like GROUP BY then you have do:
SELECT f1, f2, f3, SUM(f4)
FROM table1
GROUP BY f1,f2,f3;

But there is no direct way to achieve what you want, need some trick. Give some time I'll get you some example how to do that.