Re: Why is "group by" obligatory in SQL?

"Hans Mayr" <mayr1972_at_gmx.de> wrote in message news:340ae2ba-d78e-4a74-9da6-8b7396885bcb_at_w41g2000yqb.googlegroups.com...
> Hello,
>
> I still don't know if there is a better group to post general
> questions about SQL and the background. So I hope nobody minds ;-)
>
> My question is: Why do you have to state the GROUP BY explicitly in
> SQL? Why isn't it enough to write "select a_field, sum(b_field) from
> c_table;"? What additional value is generated by "group by a_field"?
>
> Thanks and best,
>
> Hans

It's a good question. I can't say why the language designers made the choices that they did. But I see some problems with asking the parser to infer a GROUP BY when necessary.

Consider the following

select Last_Name, substring (First_Name, 1, 3) from Persons;

Grouping by last name would be a mistake in this case. "substring" is not an aggregating function. So the parser would have to be able to distinguish between aggregating and non aggregating functions. How would this work when user defined functions can be added to the mix?

Another problem is how you interpret a select with no functions at all. Consider this:

select Last_Name, First_Name from Persons;

Should this be grouped by Last_Name and First_Name? Doing so would yield the same answer as

select distinct Last_Name, First_Name from Persons;

But as things stand, it yields the same result as

select all Last_Name, FirstName from Persons;

For some reason that eludes me, the SQL designers chose the default keyword here to be "all" rather than "distinct". Maybe they were thinking of execution speed, in the absence of any really good optimizer.

If you left the meaning of a query with no functions as is, it would seem to be inconsistent with the construct you asked for in your OP. That's my subjective response, anyway. Received on Thu Jul 23 2009 - 14:27:04 CEST