Get value of column related to value of column in other rows

I’ve a current table like this

and would like to obtain this desired table:

As you can see, this desired table should have a fourth column which indicates me the proportional value of the price in this row in relation with the rest of values of the row for the same product

For example, for product type A I have three products (1,2 and 3), and the prices are, respectively, 20, 20 and 30.

In this case, for product 1 I’d like to obtain its weight as (price of product/sum of prices of the products that belong to product A). How is this possible?

Thanks

Answer

We can try using SUM() as an analytic function here:

SELECT
    PRODUCT,
    PRODUCT_TYPE,
    PRICE,
    1.0 * PRICE / SUM(PRICE) OVER (PARTITION BY PRODUCT_TYPE) AS WEIGHT
FROM yourTable;

The idea here is that the above SUM() is a window function which computes the sum of the total price for each product type. This is then used to normalize each price, for each record.

Demo