Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

-- SQL -- GROUP BY clause: non-aggregate fields mandate

1001481Apr 7 2013 — edited Apr 18 2013
Hello,

I was studying Databases, (particularly the retrieval of the data), and found something interesting.

While using an Aggregate Function in the SELECT clause, it is mandatory to have all the non-aggregate fields in the SELECT clause to be there in the GROUP BY clause.
For example,

SELECT dept_no, SUM(salary)
FROM employee
GROUP BY dept_no;

The above SQL works fine.
But, what if the user misses the dept_no in the GROUP BY clause or he/she misses the GROUP BY clause itself?
Certainly, it is an error.

Why is this error not handled by the database. I mean, the database should be smart/intelligent enough to add the GROUP BY clause by itself. So suppose, if I miss out the GROUP BY clause or miss a non-aggregate field from the SELECT clause when I am having at least one aggregate function on a field with at least one non-aggregated field in the SELECT clause, the database should check the GROUP BY clause at time of compilation and add the mandate missed out fields in the GROUP BY clause.

Example,

SQL1:_
SELECT dept_no, SUM(salary)
FROM employee
GROUP BY dept_no;

SQL2:_
SELECT dept_no, SUM(salary)
FROM employee;

Here, the SQL1 and SQL2, both should give me same outputs without an error.

I am unable to understand why is this not handled?
This post has been answered by Frank Kulash on Apr 13 2013
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 16 2013
Added on Apr 7 2013
8 comments
4,730 views