Column containing the product of elements in a second column, sorted by group

I have the following SQL script,

Select To_Char(A1.Date1, 'dd-mm-yyyy') As  Date, B1.bm, A1.Number
From A1 
Inner Join Benchmarkdefs B1 On (A1.Bmik = B1.Bmik);

, which yields,

Date        BM  Number
11-08-2021  AA  1
12-08-2021  AA  2
13-08-2021  AA  3
14-08-2021  AA  4
15-08-2021  AA  5
11-08-2021  BB  12
12-08-2021  BB  13
13-08-2021  BB  14
14-08-2021  BB  15
15-08-2021  BB  16
11-08-2021  CC  22
12-08-2021  CC  23
13-08-2021  CC  24
14-08-2021  CC  25
15-08-2021  CC  26

I wish to create a column, Product, that contains the product of Numbers on each date within the same BM group:

Date        BM  Number  Product sum             
11-08-2021  AA  1       120             
12-08-2021  AA  2       120             
13-08-2021  AA  3       120             
14-08-2021  AA  4       120             
15-08-2021  AA  5       120             
11-08-2021  BB  12      524160              
12-08-2021  BB  13      524160              
13-08-2021  BB  14      524160              
14-08-2021  BB  15      524160              
15-08-2021  BB  16      524160              
11-08-2021  CC  22      524160              
12-08-2021  CC  23      7893600             
13-08-2021  CC  24      7893600             
14-08-2021  CC  25      7893600             
15-08-2021  CC  26      7893600

To clarify, for BM =’AA’ the product should be 1*2*3*4*5=120.

Thank you in advance! Best regards,

Answer

There isn’t a built-in product function; but you could use the exp/ln technique from this question, and adapt it to use an analytic sum:

select to_char(a1.date1, 'dd-mm-yyyy') as date1, b1.bm, a1.number1,
  round(cast(exp(sum(ln(cast(a1.number1 as binary_double))) over (partition by b1.bmik)) as number)) as product
from a1 
inner join benchmarkdefs b1 on (a1.bmik = b1.bmik);

DATE1	BM	NUMBER1	PRODUCT
12-08-2021	AA	2	120
11-08-2021	AA	1	120
13-08-2021	AA	3	120
14-08-2021	AA	4	120
15-08-2021	AA	5	120
12-08-2021	BB	13	524160
11-08-2021	BB	12	524160
13-08-2021	BB	14	524160
15-08-2021	BB	16	524160
14-08-2021	BB	15	524160
11-08-2021	CC	22	7893600
12-08-2021	CC	23	7893600
14-08-2021	CC	25	7893600
13-08-2021	CC	24	7893600
15-08-2021	CC	26	7893600

db<>fiddle showing some of the intermediate steps.

The column names in your example are illegal so I assume you just changed those for posting; so I’ve modified them slightly to make them valid.

Note that if your number can be zero then you may need to exclude that to prevent it erroring; if you have negative values you’ll need to handle those differently too; and you might want to adjust the round() precision depending on your real data too.