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!

Double aggregation in a single query block doesn't make any sence.

Vadim Tropashko-OracleFeb 15 2007 — edited Feb 16 2007
How can I argue with something that apparently has been cast in stone by ANSI SQL committee? Well the answer is famous: "Search any park in any city: you'll find no statue of committee".

OK, why

select count(1) from (
select deptno from emp
group by deptno
)

is an easy to understand query, and why

select count(count(*)) from emp
group by deptno

is not? I already mentioned one reason why count shouldn't accept any arguments, therefore count(count(*)) is a nonsence.

The other reason is that aggregation without grouping is essentially aggregation within a single group. Once you realize that

select sum(1) from emp

is the same as

select sum(1) from emp
group by -1

(where -1 or any other constant for that matter is a dummy pseudocolumn), then it becomes obvious that what we are doing in the infamous

select count(count(*)) from emp
group by deptno

is a query with two blocks

select count(1) from (
select deptno from emp
group by deptno
) group by -1

We are not allowed to combine two "group by" into a single query, aren't we?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 16 2007
Added on Feb 15 2007
3 comments
1,790 views