Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> SV: help on aggregate functions

SV: help on aggregate functions

From: Jesper Haure Norrevang <>
Date: Tue, 10 Feb 2004 07:52:37 +0100
Message-id: <000001c3efa2$78935dd0$4a28e282@AIDA.local>

Hi Rachel,

Oracle has an interface, which makes it possible, that you can write = your
own User-Defined Aggregate Functions. The hard way to get a feeling with = how
aggregate functions are working is to read chapter 11 "User-Defined Aggregate Functions" in "Oracle9i Data Cartridge Developer's Guide, Part = No.
A96595-01", and then try and test it.

Anyway, let me try to explain how an aggregate function is processed = with a
simple example
select deptno, sum(sal)
from emp
where job !=3D 'MANAGER'
group by deptno
having sum(sal) > 6500
order by deptno

  1. A row source with the columns DEPTNO and SAL provides data from the = emp table - just like a normal query. Only rows with job !=3D 'MANAGER' are contained in the query. I.e. the WHERE-clause is evaluated first. There = are 14 rows in emp. After filtering with the WHERE-clause we have 11 rows = left.
  2. The data is sorted by the DEPTNO column. I.e. the GROUP BY-clause is processed next.
  3. For each different DEPTNO, the SUM is calculated. Any rows with = NULLs in SAL are ignored. COUNT(*) is actually an exception to the generel rule, = that NULL-values are ignored in aggregate functions. After this step we have = 3 rows representing the departments 10, 20 and 30. (This step can be = broken down into 4 sub steps: Initialize, Iterate, Merge and Terminate. See the manual mentioned above for details).
  4. The HAVING-clause is evaluated after calculating the aggregate = values. The HAVING-clause eliminates department 10, and two rows are left.
  5. The ORDER BY-clause is executed. Do not rely on the sort in step 2. = If you want data in a specific order, the only way to be sure is to specify ORDER BY.
Jesper Haure N=F8rrevang

-----Oprindelig meddelelse-----
Fra: =
[] P=E5 vegne af Rachel Carmichael
Sendt: 9. februar 2004 21:52
Til: freelists oracle-l
Emne: help on aggregate functions

>From a friend of mine:

"what I'm looking for is a detailed breakdown on how aggregate functions process in the database. kind of a step-by-step to how they roll through the system"

I know that *I* don't know the answer. But I know someone on this list likely does.

Help please?


Do you Yahoo!?
Yahoo! Finance: Get your refund fast by filing online.

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at

Please see the official ORACLE-L FAQ:
To unsubscribe send email to:
put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Tue Feb 10 2004 - 00:52:37 CST

Original text of this message