Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 504
  • Last Modified:

is there a product function in oracle

Want to be able to use product functionality as in excel.
Example..
Select * from mytable t1
Say this returns...
Cpy  Year       Amount
A      2008        10
A      2008         20
A      2009        40
B      2008         1
B      2008         2
B      2008         3
etc etc
So, I want to return a data set grouped by Cpy then Year with PRODUCT values
Cpy   Year    Amount
A      2008   200
A      2009     40
B      2008       6

Is this possible without using PLSQL
Regards
0
philsivyer
Asked:
philsivyer
3 Solutions
 
Christoffer SwanströmPartnerCommented:
If the amounts are always > 0 you can do

SELECT
  cpy
  ,year
  ,exp(sum(ln(amount)))
FROM
  mytable
GROUP BY
  cpy
  ,year
0
 
sdstuberCommented:
You can also create your own aggregate function, which doesn't require positive inputs.

In fact, a product aggregate is one of the examples in the article below


http://www.experts-exchange.com/Database/Oracle/A_9391-How-to-Create-User-Defined-Aggregates-in-Oracle.html


The aggregate itself is pl/sql, but is usable within sql like other aggregates

Using the builtin sql functions should be more efficient though if your inputs are legal
0
 
sujith80Commented:
There is not built in product function within oracle. However you can do it with a recursive subquery factoring technique.

See below -

SQL> select * from tbl1;

CPY                        YEAR     AMOUNT
-------------------- ---------- ----------
A                          2008         10
A                          2008         20
A                          2009         40
B                          2008          1
B                          2008          2
B                          2008          3

6 rows selected.

SQL> with data as (
  2  select cpy, year, amount,
  3  row_number() over (partition by cpy, year order by(1)) rn,
  4  count(1) over (partition by cpy, year order by(1)) cn
  5  from tbl1
  6  ),
  7  x(cpy, year, amount, rn, cn) as (
  8  select cpy, year, amount, rn, cn
  9  from data
 10  where rn = 1
 11  union all
 12  select data.cpy, data.year, data.amount * x.amount, data.rn, data.cn
 13  from data, x
 14  where data.cpy = x.cpy
 15  and data.year=x.year
 16  and data.rn = x.rn+1)
 17  select cpy, year, amount
 18  from x
 19  where rn = cn
 20  order by cpy, year;

CPY                        YEAR     AMOUNT
-------------------- ---------- ----------
A                          2008        200
A                          2009         40
B                          2008          6

SQL>

Open in new window

0
 
philsivyerAuthor Commented:
Thanks everbody - great help
0
 
sujith80Commented:
what is the solution that you used?
Did it completely resolve your problem?
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now