select and group by - navathe

Question

a) every column that appears in group by clause must appear in select statement.

b) every column that appears in select statement must appear in group by clause

out of these two what is a valid argument?

Answer 1

Both statements are FALSE:

   Reference: Stackoverflow

   Relation : Table

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

 

 Select A,count(*) from Table group by A,B  

This will make both statements false.

But  " Select A,C,count(*) from Table group by A,B  " is not allowed it violetes the First Normal Form And it is undefined behaviour since DBMS from vendors apply different-different method to solve this problem.

Comments

@papesh Sir,

How both statements are false?

$S1$ is false. https://stackoverflow.com/questions/14603977/must-all-attributes-used-in-the-group-by-clause-appear-in-select-clause

But $S2$  is true, Even in the link from where you have taken the example is saying it true.

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?

https://stackoverflow.com/questions/5986127/do-all-columns-in-a-select-list-have-to-appear-in-a-group-by-clause

Please rectify it sir if something is missing in my understanding !!