sql - Do all columns in a SELECT list have to appear in a GROUP BY clause

Question

My lecturer stated:

All column names in SELECT list must appear in GROUP BY clause unless name is used only in an aggregate function

I'm just wanting some confirmation of this as I cannot think of a logical explanation as to why it should be true...

Answer 1

Imagine the following:

    A    B    C
   Cat   10   False
   Dog   25   True
   Dog   20   False
   Cat   5    False

If you select A, B and Group By Only A - what would your output be? You'd only have two rows (or tuples) because you have two values for A - but how does it display B?

If you group by A, B, you'd get four rows, no problems there. If you group by A and perform a function on B - like SUM(B) then you get two rows again:

    Cat    15
    Dog    45

But if you select A, B and only group by A - it doesn't know what to do. Truthfully, I believe there are some databases out there that will select a random value for B in that case and I believe there are some that will give you an error message.