Is There a Product Function Like There Is a Sum Function in Oracle SQL

is there a PRODUCT function like there is a SUM function in Oracle SQL?

select exp(sum(ln(col)))
  from table;

edit:

if col always > 0

Product() aggregate function

Oracle has exposed these ODCI (Oracle Data Cartridge Interface) methods to do the Aggregate! Reference:

• ODCIAggregateDelete() - Removes an input value from the current group.
• ODCIAggregateInitialize() - Initializes the aggregation context and instance of the implementation object type, and returns it as an OUT parameter.
• ODCIAggregateIterate() - Iterates through input rows by processing the input values, updating and then returning the aggregation context.
• ODCIAggregateMerge() - Merges two aggregation contexts into a single object instance during either serial or parallel evaluation of the user-defined aggregate.
• ODCIAggregateTerminate() - Calculates the result of the aggregate computation and performs all necessary cleanup, such as freeing memory.
• ODCIAggregateWrapContext() Integrates all external pieces of the current aggregation context to make the context self-contained.

Code For PRODUCT() Aggregate function :

CREATE OR REPLACE type PRODUCT_IMPL
AS
  object
  (
    result NUMBER,
    static FUNCTION ODCIAggregateInitialize(sctx IN OUT PRODUCT_IMPL)
    RETURN NUMBER,
    member FUNCTION ODCIAggregateIterate(self  IN OUT PRODUCT_IMPL,
                                         value IN NUMBER)
    RETURN NUMBER,
    member FUNCTION ODCIAggregateTerminate( self IN PRODUCT_IMPL,
                                            returnValue OUT NUMBER,
                                            flags IN NUMBER)
    RETURN NUMBER,
    member FUNCTION ODCIAggregateMerge(self IN OUT PRODUCT_IMPL,
                                       ctx2 IN PRODUCT_IMPL )
    RETURN NUMBER );
  /
  /* 1.Initializes the computation by initializing the aggregation context—the rows over which aggregation is performed: */
CREATE OR REPLACE type body PRODUCT_IMPL
IS
  static FUNCTION ODCIAggregateInitialize(sctx IN OUT PRODUCT_IMPL)
  RETURN NUMBER
IS
BEGIN
  sctx := PRODUCT_IMPL(1);
  RETURN ODCIConst.Success;
END;
/* 2.Iteratively processes each successive input value and updates the context: */
member FUNCTION ODCIAggregateIterate(self  IN OUT PRODUCT_IMPL,
                                     value IN NUMBER)
  RETURN NUMBER
IS
BEGIN
  self.result := value * self.result;
  RETURN ODCIConst.Success;
END;
member FUNCTION ODCIAggregateTerminate(
    self IN PRODUCT_IMPL,
    returnValue OUT NUMBER,
    flags IN NUMBER)
  RETURN NUMBER
IS
BEGIN
  returnValue := self.result;
  RETURN ODCIConst.Success;
END;
member FUNCTION ODCIAggregateMerge(self IN OUT PRODUCT_IMPL,
                                   ctx2 IN PRODUCT_IMPL)
  RETURN NUMBER
IS
BEGIN
  self.result := self.result;
  RETURN ODCIConst.Success;
END;
END;
/

/* Create A function using the PRODUCT_IMPL implementation we did above */
CREATE OR REPLACE FUNCTION product(input NUMBER)
RETURN NUMBER 
PARALLEL_ENABLE AGGREGATE USING PRODUCT_IMPL;
/

Results:

SELECT group_name,product(num) FROM product_test GROUP BY group_name;

Mahesh  -60000
Mahesh_1    9

Product in a GROUP BY with potential zeroes

First, log(0) is not undefined - it's negative infinity.

Second: in Oracle you can generate a negative infinity, but you'll have to use BINARY_FLOAT.

select a, b, c,
       sum(d) d,
       sum(e) e,
       exp(sum(CASE WHEN f <> 0 THEN ln(f) ELSE -1/0F END)) f
  from x
  group by a, b, c;

Using your data this generates:

A   B   C   D   E   F
foo bar hoo 6   11  0
foo bar mee 4   6   6.0000001304324524E+000

Note that introducing logarithms and power functions will introduce some rounding issues, but this should at least get you started.

dbfiddle here

TO NEGATIVE INFINITY...AND BEYOND!!!!!!

:-)

Oracle SQL Cumulative Product

The value of Initial_Value must be multiplied to the result of the SUM() window function, so the correct expression is:

0.88 * EXP(SUM(LN((1 + pct_change))) OVER (ORDER BY report_month))

See the demo.

Why is there no PRODUCT aggregate function in SQL?

There is no PRODUCT set function in the SQL Standard. It would appear to be a worthy candidate, though (unlike, say, a CONCATENATE set function: it's not a good fit for SQL e.g. the resulting data type would involve multivalues and pose a problem as regards first normal form).

The SQL Standards aim to consolidate functionality across SQL products circa 1990 and to provide 'thought leadership' on future development. In short, they document what SQL does and what SQL should do. The absence of PRODUCT set function suggests that in 1990 no vendor though it worthy of inclusion and there has been no academic interest in introducing it into the Standard.

Of course, vendors always have sought to add their own functionality, these days usually as extentions to Standards rather than tangentally. I don't recall seeing a PRODUCT set function (or even demand for one) in any of the SQL products I've used.

In any case, the work around is fairly simple using log and exp scalar functions (and logic to handle negatives) with the SUM set function; see @gbn's answer for some sample code. I've never needed to do this in a business application, though.

In conclusion, my best guess is that there is no demand from SQL end users for a PRODUCT set function; further, that anyone with an academic interest would probably find the workaround acceptable (i.e. would not value the syntactic sugar a PRODUCT set function would provide).

Out of interest, there is indeed demand in SQL Server Land for new set functions but for those of the window function variety (and Standard SQL, too). For more details, including how to get involved in further driving demand, see Itzik Ben-Gan's blog.

Aggregate function to multiply all values

Yes, there's a technique described here in this blog:

Basically you take the natural log of SUM and then do an exponential (e^x)

SELECT EXP (SUM (LN (col))) as product from t;

Since the output of this would be a floating point, you may do a FLOOR

FLOOR( EXP (SUM (LN (col))) ) 

DEMO

Note: I've just found that this would fail if one of the rows has 0. so you should use a separate condition or a with clause that if one of them is zero the product should be zero.

How sum with case conditional statement works in sql

Presumably, this is the part that you are struggling to understand:

  select deptno,
         sum(case when jobname = 'Analyst' then 1 else 0 end) as numAnalysts
  from employees
  group by deptno

This is a simple aggregation query, really. What the query is doing is:

• Look at each row in employees
• If jobname is 'Analyst' then assign the value of 1 (this is the case statement. Otherwise, assign a value of0`.
• Aggregate by department, summing the value just calculated. This has the effect of counting the number of analysts.

case is an expression that returns a value. The sum() is simply adding up that value for each group.

Get sum of multiplication of two columns of two tables

Just use something like that and adjust it for your particular need. Nothing more complex neccesary here. You'll get the idea.

All you need is aggregate functions. See documentation.

select * from (
SELECT customer_id, sum(number_ordered*(pack_prize/pack_size) as totalvalue
FROM customers 
natural join orders 
natural join items 
natural join products 
group by customer_id order by totalvalue desc
) WHERE rownum <= 5;

---

Related Topics