Get percentage of all rows in a similar grouping

Question

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

Answer 1

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