Over

[chrisssyp82]

I have created a temporary table and i wish to aggregate the maket value into two columns called Total Fund Value and Cash/Cash Equivalents.

Fot Total Fund Value i've created this in the select:

SUM(p.valval_alt_cmb_amt) OVER (PARTITION BY RTRIM(a.acct_id)) AS [Total_Fund_Value]

However for Cash/Cash Equivalents i'm unsure on how to create the sum. Initially i had an update to the temporary table which looked like this to cover both sums:

UPDATE #PositionsTable
		SET 
			[Total_Fund_Value]			= tblSUM_Fund.[Market_Value]
		,	[Cash/Cash_Equivalents]		= tblSUM_Cash.[Market_Value]
		
		FROM #PositionsTable AS pt
			INNER JOIN
				(
					SELECT
						[Internal_Portfolio_Code]
					,	SUM([Market_Value])			AS [Market_Value]
					FROM #PositionsTable
					GROUP BY [Internal_Portfolio_Code]
				) AS tblSUM_Fund
				ON tblSUM_Fund.[Internal_Portfolio_Code] = pt.[Internal_Portfolio_Code]
				
			INNER JOIN 	
				(
					SELECT
						[Internal_Portfolio_Code]
					,	SUM([Market_Value])			AS [Market_Value]
					FROM #PositionsTable AS pt
						INNER JOIN issue_dg AS i
							ON i.pref_iss_id = pt.[Security_ID SEDOL/Internal]
					WHERE i.issue_cls1_cde = '010'
					GROUP BY [Internal_Portfolio_Code]
				) AS tblSUM_Cash
				ON tblSUM_Cash.[Internal_Portfolio_Code] = pt.[Internal_Portfolio_Code]

So how can i do the equivalent which i have created for Total Fund Value for Cash/Cash Equivalents based on the 2nd piece of code above?

Many Thanks

 

[markros]

Use case based sum, e.g.

SUM(case when Issue_cls1_cde = '010' then [Market_Value] else 0 end) OVER (partition by a.acct_id)

BTW, why do you need to use trim function here?

PluralSight Learning Library

 

[chrisssyp82]

Just use it just in case, bit of a habit.

Thank you for your help, worked perfectly