This feature is sure to define your placement into one of the following camps:
- I love it when my IDE helps me with my code
- I hate it when my IDE inserts code I didn’t explicitly type
And the feature is –
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:
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?