Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

jdavidb (1361)

jdavidb
  (email not shown publicly)
http://voiceofjohn.blogspot.com/

J. David Blackstone has a Bachelor of Science in Computer Science and Engineering and nine years of experience at a wireless telecommunications company, where he learned Perl and never looked back. J. David has an advantage in that he works really hard, he has a passion for writing good software, and he knows many of the world's best Perl programmers.

Journal of jdavidb (1361)

Friday April 13, 2007
10:09 AM

GROUP BY

[ #32983 ]

The GROUP BY clause of SQL queries is completely redundant. All of the information in it can be derived from the rest of the query. Redundancy in communication is a great thing for human communication, to prevent misunderstandings. It allows for error correction. But it's not a great thing when I'm hand-typing such a verbose language.

GROUP BY should be made optional. In some cases it might allow the SQL parser to detect an error I've made in my query. And in some cases it might be necessary (but so far I can't think of any). I'd probably want to leave it in to programs that I am going to leave lying around. But for queries I'm throwing into an interactive system, I don't want to deal with it.

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • Machine communication is not human communication.

    SQL is not about reducing redundancy.

    SQL is about offloading work related to data storage retrieval to a dedicated machine.

    Should I have to implement my own grouping code in every single application?
    • Should I have to implement my own grouping code in every single application?

      No, I think his basic idea is that the database should be able to deduce the GROUP BY clause from the rest of the statement. Now, a database complains if you output a column that isn't in an aggregate function, or in the GROUP BY clause. Instead, it could implicitly add the column to the GROUP BY clause.

      I don't think that overall it would save much typing. And having to be explicit has its advantages, in cases the SQL statement implies a different grouping than what you had in mind.

  • SELECT foo, bar, SUM(quux) FROM qux GROUP BY foo, bar;

    SELECT foo, bar, SUM(quux) FROM qux GROUP BY bar, foo;

    SELECT foo, SUM(quux) FROM qux GROUP BY foo, bar;

    SELECT foo, SUM(quux) FROM qux GROUP BY bar;
    • Maybe I need the comments to clue me in. The last two won't work ... is your point that in such an instance having the GROUP BY allows the programmer to figure out what the previous programmer meant and correct? Because I thought I clearly limited myself to talking about interactive, one-shot queries. This is like requiring strict for a perl-one liner. :)

      As near as I can tell, there is no difference between the first two, although I'm willing to be enlightened. I may just be sleepy on this early Monda

      --
      J. David works really hard, has a passion for writing good software, and knows many of the world's best Perl programmers
      • Why won’t the last two work?

        As for the first two, you’re right, there is no difference. Order matters when you use window functions, but not in ordinary grouping.

        • Never mind, they will. It's just not something I do often. And I think you've shown me why that clause can't be made optional. Of course, I'd prefer that it be made optional and that expressing the fields to group by in the SELECT be made mandatory, but I guess that would probably bork a lot of people up. :)

          --
          J. David works really hard, has a passion for writing good software, and knows many of the world's best Perl programmers