Is there a way to distribute values in one column of null values in other column in SQL query?-CodePudding

What I have

region	district	subs
NULL	NULL	500
Akmola	Kokshetau	150
Almaty	Taldykorgan	500
Aktobe	Aktobe	400

What I want to get

region	district	subs
Akmola	Kokshetau	316
Almaty	Taldykorgan	666
Aktobe	Aktobe	566

Is there a way to equally distribute value in column (subs / revenue) that is NULL in other column (district), among other values that are not null in the same column.

CodePudding user response：

Not sure I fully understand the question, but I guess you want something like:

select region
     , district
     , subs   (select subs/(select count(1) from t where region is not null) from t where region is null)
from t
where region is not null;

region  district    (No column name)
Akmola  Kokshetau   316
Almaty  Taldykorgan 666
Aktobe  Aktobe      566

CodePudding user response：

Try this :

SELECT region
     , district
     , subs   sum(subs) FILTER (WHERE region IS NULL) OVER() / count(*) FILTER (WHERE region IS NOT NULL) OVER () :: integer AS subs
  FROM your_table