This feature is sure to define your placement into one of the following camps:

  1. I love it when my IDE helps me with my code
  2. I hate it when my IDE inserts code I didn’t explicitly type

STOP! If you’re using version 4 or higher of SQL Developer, Click Here.

And the feature is –

Autogenerate Group By clause

Autogenerate Group By clause

The help defines this preference/feature as:

Automatically generates a GROUP BY clause if you manually enter (not copy/paste) a SELECT statement containing a COUNT function, and then edit the SELECT query.

I think that’s mostly correct. It’s not just for COUNT, but for any AGGREGATE function. I tested SUM, AVG, and COUNT for good measure.

So what does this mean exactly?

If I type:

SELECT avg(Salary)
FROM Hr.Employees;

This will ‘work.’ You’re asking for an average salary of everyone in the Employees table.

But if I ask for this:

SELECT avg(Salary), Department_Id
FROM Hr.Employees;

Oracle will give me a

ORA-00937: not a single-group group function
00937. 00000 -  "not a single-group group function"
*Cause:    
*Action:
Error at Line: 2 Column: 20

This isn’t a math tutorial, but the SQL can’t read our mind and know that we want average salaries broken down by departments. To achieve that we implement the GROUP BY clause.

Now some of you might be thinking, wouldn’t it be nice if SQL Developer could see that I was doing a aggregate and automatically add for me the required GROUP BY.

For that crowd, you’ll want to enable the autogenerate preference. Here’s how it looks:

SQL Developer auto-inserts and updates your GROUP BY clause as you change the SELECT list following an aggregate function call.

So what camp do I belong to?

I fall somewhere in the middle. I like the code insight features, but I disable the automatic bits and just call it on demand with ctrl+spacebar. I didn’t have this auto-Group by feature enabled, and I’ll probably leave it off. But that’s just me. What do you think?

Updated for Versions 4.0 and Higher

There’s no more debate. The automatic behavior caused more problems than it solved. So we got rid of it. Just the ‘automatic’ part, mind you.

Now when the parser sees a potential grammar mistake, it lets you know.

note the 2nd grammar squiggle, mouse over that...
note the 2nd grammar squiggle, mouse over that…

If you agree with the suggestion, click on the blue text bit of the message.

The new red highlighted text is inserted for me - only after I asked for it!
The new red highlighted text is inserted for me – only after I asked for it!

Since we took out the automatic Group By doo-hickey, we no longer need an option to turn that on and off. You can still turn off the grammar check ‘squiggles’ and also change the color/line style of the squiggles in the preferences.

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

5 Comments

  1. I really wish there was a way to do this with a keyboard shortcut instead of the mouse.

  2. I tried this today and the grammar checkers stopped working after references like this without double quotes
    @c:\scripts\my.sql

    And the auto-group by didn’t handle nvl2 very well, got this for the column.
    nvl2(c.email,’t-Button

    And it still picked up grammar issuees once I corrected and successfully executed with new group by.

    (4.1.1.19)

  3. I have that feature enabled and like it, but recently hit a bug. In a window with multiple SQL statements, I added a field to an existing “count” select. SQL Developer generated not one but, like, 8 group by clauses for the select. I had to manually delete the extras. Adding another field produced the same result.

    • Hey Dan,

      I stepped on that bug myself just today, hence the blog post 🙂

      It appears that the triggering mechanism is to have the multiple statements. I’ve got this logged to be fixed ASAP.

Reply To Scott Cancel Reply