How to write SQL to achieve the required output

I have an existing table with the following columns: data_date,portfolio,weight,comments and I will need to generate an additional field called 'factor' by doing some calculation using the existing fields.

(Data shown below is just a sample, there are millions of records)

data_date	portfolio	weight	comments	factor
2020-01-02	A	0.2	Exclude	?
2020-01-03	B	0.3	-	?

The 'factor' field is calculated using the formula:

(select sum(weight) from TABLE_A 
  where comments != 'Exclude' group by data_date,portfolio) 
divide by
(select sum(weight) from TABLE_A group by data_date,portfolio)

How can I write a select statement such that it will include all the existing 4 columns with the nicely calculated factor field?

Any help or suggestion is appreciated! Thank you!

Tried with multiple with clause but still can't get the results right.

Solution

You could use sum window function as the following:

Select data_date, portfolio, weight, comments,
   Sum(Case When comments != 'Exclude' Then weight Else 0 End) Over (Partition By data_date,portfolio) /
   Sum(weight) Over (Partition By data_date,portfolio) As factor
From TABLE_A
Order By data_date, portfolio

See a demo.