postgresql - How to remove unknown entries and distribute its numeric value across other known entries?

Question

I've been trying to wrap my head around this, but I cannot seem to find an easy solution:

I have a table with known and unknown entries. Each entry has a column with a numeric value representing it. How do I remove the unknown entries, and equally distribute its numeric value across other known entries?

Here is an example:

Original table:

|Item | Size | Count |
|-----|------|-------|
|A    | null |  20   |
|A    | M    |   5   |
|A    | XL   |  10   |
|B    | null |  12   |
|B    | M    |  8    |

Desired output:

|Item | Size | Count |
|-----|------|-------|
|A    | M    |  15   |
|A    | XL   |  20   |
|B    | M    |  20   |

Basically, I want to remove the items with null sizes, and evenly distribute its count across other sizes for that same item.

Would be great if I could get some help! Thanks in advance

Answer 1

step-by-step demo:db<>fiddle

SELECT 
    item,
    size,
    cnt + (sum / count)                 -- 2
FROM (
    SELECT                              -- 1
        *,
        SUM(cnt) FILTER (WHERE size IS NULL) OVER (PARTITION BY item),
        COUNT(cnt) FILTER (WHERE size IS NOT NULL) OVER (PARTITION BY item)
    FROM
        mytable
) s
WHERE size IS NOT NULL                  -- 3

1. Use SUM() and COUNT() window functions to find the amount to split up (= all cnt values of NULL records) and the number of split parts (= all non-NULL records).
2. Add the split parts to the current cnt value
3. Filter all non-NULL records