In PostgreSQL, how can I get the average of all other rows (e.g., "val") in the same group (e.g., "retailer") while still outputting all rows?
For example, below, I want to get the percentage of the val column per row compared to all other rows with the same category. And I want to do this for each category.
Original table:
id | category | val
----------------------------
1 retailer 3
2 retailer 2
3 customer 1
4 retailer 5
5 customer 7
Example:
id | category | val | output
----------------------------
1 retailer 3 .3 (retailer1 value + all other retailer values / total values of retailers)
2 retailer 2 .2 (retailer2 value + all other retailer values / total values of retailers)
3 customer 1 .125 (1 / 1 + 7)
4 retailer 5 .5 (5 / 3 + 2 + 5)
5 customer 7 .875 (7 / 1 + 7)
Complete output:
id | category | val | output
----------------------------
1 retailer 3 .3
2 retailer 2 .2
3 customer 1 .125
4 retailer 5 .5
5 customer 7 .875
Use window functions:
select t.*, val::numeric / nullif(sum(val) over(partition by category), 0) res
from mytable t
The window sum() computes the total val over rows having the same category.
Notes:
it looks like val is an integer; if so, we need to cast at least one of the values to a decimal to avoid integer division
nullif() avoids the division by zero error if all categories have 0 values