This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Mar 7, 2013 8:48 PM by BS2012

# What kind of input parameter is used in SUM function?

Currently Being Moderated
Hi Everyone,
As we know sum is a predefined oracle function. But what these oracle guys have used for the input parameters. How they have done this?
I mean we can write this sum fuction like so many ways like as mentioned below. Please give me some ideas how to do that.
``````SELECT SUM(salary) as "Total Salary" FROM employees;

SELECT SUM(DISTINCT salary) as "Total Salary" FROM employees;

SELECT SUM(income - expenses) as "Net Income" FROM gl_transactions;

SELECT SUM(sales * 0.10) as "Commission" FROM order_details;``````
Regards,
BS2012
• ###### 1. Re: What kind of input parameter is used in SUM function?
Currently Being Moderated
Hi,

If you observe:

salary --- number

distinct salary -- number

income - expenses --- number

sales*0.10 --- number..

so sum basically adds up all those numbers it gets in its list.. thats it :)

Cheers,
Manik.
• ###### 2. Re: What kind of input parameter is used in SUM function?
Currently Being Moderated
Hi Manick,
Thanks for your reply. But my question is how it is written? I mean the technical aspects of SUM function. I am not able to think about that. So I've asked this in forum.

Regards,
BS2012
• ###### 3. Re: What kind of input parameter is used in SUM function?
Currently Being Moderated
SUM accepts a expression as a input. The expression should evaluate to a number.
``````
SUM(salary)
SUM(income - expenses)
SUM(sales * 0.01) ``````
In all the above the expression is evaluated as a numeric value and passed to the sum function.

DISTINCT is an additional characteristics of SUM function where in it picks only the unique evaluated expression values.
• ###### 4. Re: What kind of input parameter is used in SUM function?
Currently Being Moderated
Hi Manick and Everyone,
Whenever we talk about any predefined or user defined function, I am wondering about the way of writing a plsql function with the same functionality.
I mean when we are writing like select sum(sal) from employees;....then something is going to check not only for that column but also in that table as well.
How can we implement such functionality in a user defined function? I am running out of ideas and thoughts. Please share something.

Regards,
BS2012
• ###### 5. Re: What kind of input parameter is used in SUM function?
Currently Being Moderated
Hi Karthick,
Please see my last post and help me.
• ###### 6. Re: What kind of input parameter is used in SUM function?
Currently Being Moderated
BS2012 wrote:
Hi Manick,
Thanks for your reply. But my question is how it is written? I mean the technical aspects of SUM function. I am not able to think about that. So I've asked this in forum.

Regards,
BS2012
To answer your Subject Line - Sum only accepts Numeric Inputs or a non Numeric datatype that can be converted into Numeric datatype.

About "how is it written?" - Does it really matter? If it matters, still the implementation is internal to Oracle and its implementation is what should be least bothered.
Its all about summing the values passed to the Function, so I cannot think of any other algorithm that will merely add the Numbers (if not direct numbers then by typecasting them internally) and return the Addition of all the Numbers.

For detailed information of Sum function.
• ###### 7. Re: What kind of input parameter is used in SUM function?
Currently Being Moderated
BS2012 wrote:
Hi Manick and Everyone,
Whenever we talk about any predefined or user defined function, I am wondering about the way of writing a plsql function with the same functionality.
I mean when we are writing like select sum(sal) from employees;....then something is going to check not only for that column but also in that table as well.
How can we implement such functionality in a user defined function? I am running out of ideas and thoughts. Please share something.

Regards,
BS2012
[url http://docs.oracle.com/cd/B28359_01/appdev.111/b28425/aggr_functions.htm] Using User-Defined Aggregate Functions
• ###### 8. Re: What kind of input parameter is used in SUM function?
Currently Being Moderated
Looks like you want to know the internal algo of SUM function.

I guess (I may be wrong) it was written in C or C++ where the input parameter is a vaiable argument list and its typecasted based on the input inside C program and different switch cases written to handle based on i/p if its a direct number or expression or a formula and then finally addition algorithm on the list is performed and o/p is thrown on the user screen. And if you ask me to write that for you for an idea... phewwwww I will admit that I am an electrical engineer who loves oracle. Thats it :)

Cheers,
Manik.
• ###### 9. Re: What kind of input parameter is used in SUM function?
Currently Being Moderated
Hi Purvesh,
I want to know about how can we write a PLSQL function with the above fuctionality. How can we implement such things in PLSQL?

Regards,
BS2012.
• ###### 10. Re: What kind of input parameter is used in SUM function?
Currently Being Moderated
BS2012 wrote:
Hi Purvesh,
I want to know about how can we write a PLSQL function with the above fuctionality. How can we implement such things in PLSQL?

Regards,
BS2012.
Using User-Defined Aggregate Functions

Here is a sample
• ###### 11. Re: What kind of input parameter is used in SUM function?
Currently Being Moderated
BS2012 wrote:
Hi Purvesh,
I want to know about how can we write a PLSQL function with the above fuctionality. How can we implement such things in PLSQL?

Regards,
BS2012.
You're not very clear. Do you mean how do you write a function to accept what looks like lots of different ways to input a parameter?
If so, then the SUM isn't really doing that. The expression in the brackets is evaluated before SUM sees it so with

SUM(col1 + col2),

col1+col2 is evaluated first and SUM only sees a single number.

If, howver, you mean, how do I write user-defined aggregation functions (like MYSUM) then see http://docs.oracle.com/cd/E11882_01/appdev.112/e10765/aggr_functions.htm#CECJAAJG
• ###### 12. Re: What kind of input parameter is used in SUM function?
Currently Being Moderated
BS2012 wrote:
Hi Everyone,
As we know sum is a predefined oracle function. But what these oracle guys have used for the input parameters. How they have done this?
I mean we can write this sum fuction like so many ways like as mentioned below. Please give me some ideas how to do that.
``````SELECT SUM(salary) as "Total Salary" FROM employees;

SELECT SUM(DISTINCT salary) as "Total Salary" FROM employees;

SELECT SUM(income - expenses) as "Net Income" FROM gl_transactions;

SELECT SUM(sales * 0.10) as "Commission" FROM order_details;``````
Regards,
BS2012
As others have said, your question is not quite clear.

There are many aspects and angles to looking at what you are asking.

Primarily, from a top-level, the sum function simply takes a number value as it's argument, so all those examples you have given have expressions in them that evaluate to a numeric value before being supplied to the sum function. (As someone else already mentioned you can have non-numeric datatypes, just so long as they can implicitly be converted to a numeric value).

From a statement parsing and execution perspective, the contents of the expression inside the brackets will be evaluated before being passed to the sum function. It is not the sum function that itself takes the expression and evaluates it. The sum function just expects a single numeric value.

Internally, what the sum function does, is more than just a single... call function and return value, because it has to deal with multiple values being passed in as part of the aggregating group. As such, it needs to have the ability to know when to start it's summing, to accept multiple values as input so it can sum them together, and to know when to stop summing inputs and pass the result back.

If we write our own user defined aggregate function (other people have already provided a link to explain such) we can see what is happening internally. In this following example, we'll write a user defined function that multiplies the values rather than sums them...
``````create or replace type mul_type as object(
val number,
static function ODCIAggregateInitialize(sctx in out mul_type) return number,
member function ODCIAggregateIterate(self in out mul_type, value in number) return number,
member function ODCIAggregateTerminate(self in mul_type, returnvalue out number, flags in number) return number,
member function ODCIAggregateMerge(self in out mul_type, ctx2 in mul_type) return number
);
/
create or replace type body mul_type is
static function ODCIAggregateInitialize(sctx in out mul_type) return number is
begin
sctx := mul_type(null);
return ODCIConst.Success;
end;
member function ODCIAggregateIterate(self in out mul_type, value in number) return number is
begin
self.val := nvl(self.val,1) * value;
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate(self in mul_type, returnvalue out number, flags in number) return number is
begin
returnValue := self.val;
return ODCIConst.Success;
end;
member function ODCIAggregateMerge(self in out mul_type, ctx2 in mul_type) return number is
begin
self.val := self.val * ctx2.val;
return ODCIConst.Success;
end;
end;
/
create or replace function mul(input number) return number deterministic parallel_enable aggregate using mul_type;
/``````
So, our user defined aggregate function is based on an aggregate object type.
This object holds a value ("val" in our example).
It has an Initialize method, so when the SQL engine indicates that it's the start of an aggregation of values it can set it's value to an initial value (in this case null).
It has an Iterate method, so as the SQL engine passes values to it as part of the aggregated set of values, it can process them (in our case it multiplies the input value with the value it already has for this set of aggregations (and takes a base value of 1 for the first iteration))
It has a Terminate method, so when the SQL engine indicates that the aggregate set of values is complete, it can return the result.
The last method in there is a Merge and is a mandatory requirement, so that when aggregation is done using parallel evaluation (to improve performance internally), the results of those parallelly executed aggregations can be combined together (see http://docs.oracle.com/cd/E11882_01/appdev.112/e10765/ext_agg_ref.htm#ADDCI5132). As we're multiplying numbers, in our case, it is simply a case of multiplying one result with the other.

And to see it working...
``````SQL> with t as (select 2 as x from dual union all
2             select 3 from dual union all
3             select 4 from dual union all
4             select 5 from dual)
5  --
6  select mul(x)
7  from t;

MUL(X)
----------
120``````
• ###### 13. Re: What kind of input parameter is used in SUM function?
Currently Being Moderated
Hi Blu,
Thanks a lot for sharing all this. Don't get confused. My question was if I need to build a same functionality in PLSQL as SUM...how can I do that?

Regards,
BS2012.
• ###### 14. Re: What kind of input parameter is used in SUM function?
Currently Being Moderated
BS2012 wrote:
Hi Blu,
Thanks a lot for sharing all this. Don't get confused. My question was if I need to build a same functionality in PLSQL as SUM...how can I do that?

Regards,
BS2012.
Which part of Blu's suggestion did you not understand? He has demonstrated how to Create a User Defined Function and chose not to provide a direct Ready - to - Eat solution, but a hint to progress. It is now your turn to think and try to implement it and ask specifics in case you are stuck.

My question to you would be, Have you tried? If yes, post your best attempt and error for people to help you.
1 2 Previous Next

#### Legend

• Correct Answers - 10 points