CODEWITHSUNDEEP
sqlpostgresqlgroup-by
belfastcowboy24
belfastcowboy24

Reputation: 317

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

I've a current table like this

current table

and would like to obtain this desired table:

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

Upvotes: 0

Views: 31

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521457

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.

enter image description here

Demo

Upvotes: 2

Related Questions