title: Oracle SQL : Calculating weighted probability
body: <p>I'm struggling to retrieve a &quot;weighted probability&quot; from a database table in my SQL statement.</p>
  <p>What do I need to do:</p>
  <p>I have tabular information of probable financial values like:</p>
  <p>Table my_table</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>ID</th>
  <th>P [%]</th>
  <th>Value [$]</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>50</td>
  <td>200</td>
  </tr>
  <tr>
  <td>2</td>
  <td>50</td>
  <td>200</td>
  </tr>
  <tr>
  <td>3</td>
  <td>60</td>
  <td>100</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I need to calculate the weighted probability of reasonable worst case financial value to occur.</p>
  <p>The formula is:</p>
  <pre><code>P_weighted = 1 - (1 - P_1 * Value_1/Max(Value_1-n) * (1 - P_2 * Value_2/Max(Value_1-n) * ...
  </code></pre>
  <p>i.e.</p>
  <pre><code>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% 
  </code></pre>
  <p>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.</p>
  <p>Hence, if I were only to calculate the combined probability I could (regardless of the value I could do like:</p>
  <pre><code>SELECT EXP(SUM(LN(1 - t.P))) FROM FROM my_table t WHERE condition
  </code></pre>
  <p>When I need to include the Max(t.Value) I've got the following problem:</p>
  <blockquote>
  <p>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.</p>
  </blockquote>
  <p>So I tried the following:</p>
  <pre><code>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 
  </code></pre>
  <p>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%.</p>
  <p>How do I get the weighted probability from by table above using (Oracle) SQL?</p>