I'm struggling to retrieve a "weighted probability" from a database table in my SQL statement.

What do I need to do:

I have tabular information of probable financial values like:

Table my\_table

| ID | P [%] | Value [$] |
| --- | --- | --- |
| 1 | 50 | 200 |
| 2 | 50 | 200 |
| 3 | 60 | 100 |

I need to calculate the weighted probability of reasonable worst case financial value to occur.

The formula is:

```
P_weighted = 1 - (1 - P_1 * Value_1/Max(Value_1-n) * (1 - P_2 * Value_2/Max(Value_1-n) * ...

```

i.e.

```
P_weighted = 1 - Product(1 - P_i * Value_i / Max(Value_1-n)

P_weighted = 1 - (1 - 50% * 200 / 200) * (1 - 50% * 200 / 200) * (1 - 60% * 100 / 200) = 82.5% 

```

I know the is not product function in (Oracle) SQL, and this can be substituted by EXP( SUM LN(x))) ensuring x is always positive.

Hence, if I were only to calculate the combined probability I could (regardless of the value I could do like:

```
SELECT EXP(SUM(LN(1 - t.P))) FROM FROM my_table t WHERE condition

```

When I need to include the Max(t.Value) I've got the following problem:

> 
> A SELECT list cannot include both a group function, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, and an individual column expression, unless the individual column expression is included in a GROUP BY clause.
> 
> 
> 

So I tried the following:

```
SELECT ROUND(1-EXP(SUM(LN(1 - t.P*t.Value/max(t.Value)))),1) FROM FROM my_table t WHERE condition GROUP BY t.P, t.Value 

```

But this does obviously group the output by probability rather than multiplying it and just returns 0.5 or 50% instead of the product which should be 0.825 or 82.5%.

How do I get the weighted probability from by table above using (Oracle) SQL?