Is SQL GROUP BY a design flaw?

sql

Solution

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

`

Problem

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?

Original source

Related problems