How to write SQL to achieve the required output-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

After trying to understand your question, I believe more data is needed. Given below is a code that you can copy paste and run directly on your SSMS window and you shall see that your dividend query/requirement gives one sum while the second query/requirement which is the divisor has two outputs.

declare @tableA table(
data_date date,
portfolio varchar(5),
[weight] numeric(18,8),
comments varchar(15),
factor varchar(5)
)

insert @tableA
select '2020-01-02','A',0.2,'Exclude','?'
union
select '2020-01-03','B',0.3,'-','?'

select sum([weight]) 
from @tableA 
where comments not in ('Exclude')
group by data_date, portfolio

Output: 0.30000000

select sum([weight])
from @tableA
group by data_date, portfolio

Output: 0.20000000 0.30000000

This is clearly because you are grouping by date and there are two different dates and Portfolios. Thus, your requirement is not clear and would need more data on what you need exactly.