SQL group by Clause please help?

Question

I woudld like this to work (this is a uni question), asking for the albumtitle and date:

select albumtitle, date
from albums left outer join date
group by albumcode

It needs to be grouped by albumcode (instead of album title since there may be 2 of the same title) but I simply can;t it gives me an error.

Answer 1

Whenever you have a GROUP BY, the only things that can appear in the SELECT list are



1) items in the GROUP BY list

2) aggregate functions (MIN, MAX, COUNT, AVG, etc.)

3) constant values



Also, having both a table and column named 'date' is a TERRIBLE idea...avoid using keywords as identifiers like the plague! Drop me a line as to your schema and exactly what you're trying to do and I may be able to make some suggestions.

Answer 2

Of course it will your SQL is lacking stuff



Using the left outer join you need to link them on the field



select a.albumtitle,b.date

from albums

left outer join loans b on a.code = b.code



Where code is the unique ref from a held in b



to begin with...

Answer 3

any field you put in the select clause that is not an aggregate, ie Sum(), Min(), Max(), etc, must be in the group by clause as well.

Answer 4

http://www.techonthenet.com/sql/group_by.php