Is SQL GROUP BY a design flaw? [closed]

Question

Why does SQL require that I specify on which attributes to group? Why can't it just use all non-aggregates?

If an attribute is not aggregated and is not in the GROUP BY clause then nondeterministic choice would be the only option assuming tuples are unordered (mysql kind of does this) and that is a huge gotcha. As far as I know, Postgresql requires that all attributes not appearing in the GROUP BY must be aggregated, which reinforces that it is superfluous.

• Am I missing something or is this a language design flaw that promotes loose implementations and makes queries harder to write?
• If I am missing something, what is an example query where group attributes can not be inferred?   

Answer 1

You don't have to group by the exactly the same thing you're selecting, e.g. :

SQL:select priority,count(*) from rule_class group by priority  
PRIORITY   COUNT(*)       70          1       50          4       30          1       90          2       10          4
  
SQL:select decode(priority,50,'Norm','Odd'),count(*) from rule_class group by priority
  
DECO   COUNT(*) Odd           1 Norm          4 Odd           1 Odd           2 Odd           4
  
SQL:select decode(priority,50,'Norm','Odd'),count(*) from rule_class group by decode(priority,50,'Norm','Odd')
  
DECO   COUNT(*) Norm          4 Odd           8