Joining 2 sum queries into 1. to dived 2

Transact-SQL
1

Hi I have the following 2 queries

[code]
UPDATE m
SET m.Marketprice= f.tempmarketvalue1
FROM [dbo].[BNYUKGSL113Table] m
INNER JOIN
(
SELECT [Counterpartyborrower],[Trade_type], [Account],SUM([MarketValueBaseCurrency]) as tempmarketvalue1
FROM [dbo].[BNYUKGSL113Table]
WHERE ( [Trade_type]='Loan')
GROUP BY [Counterpartyborrower],[Trade_type], [Account]
) f ON m.[Counterpartyborrower] = f.[Counterpartyborrower] AND m.[Trade_type] = f.[Trade_type] AND m.[Account] = f.[Account]

UPDATE m
SET m.Marketprice= f.tempmarketvalue2
FROM [dbo].[BNYUKGSL113Table] m
INNER JOIN
(
SELECT [Counterpartyborrower],[Trade_type],SUM([MarketValueBaseCurrency]) as tempmarketvalue2
FROM [dbo].[BNYUKGSL113Table]
WHERE ( [Trade_type]='Loan')
GROUP BY [Counterpartyborrower],[Trade_type]
) f ON m.[Counterpartyborrower] = f.[Counterpartyborrower] AND m.[Trade_type] = f.[Trade_type]

[\code]

what i want to do is join both queries and get the sql to divide the f.tempmarketvalue1 by f.tempmarketvalue2 where the [Counterpartyborrower] are the same to populate a field in the table with the value.

is this possible and how can i do it?

2

You are using an allias in your query, you can join a table twice if you want. So m1 and m2 for example. Or you can use a subquery to do the same.

3

not really sure what you mean. would you have an example to follow or no a good link online to loook it up.

4

It should/can be something like this:

UPDATE m
SET m.Marketprice= f1.tempmarketvalue1/f2.tempmarketvalue2
FROM [dbo].[BNYUKGSL113Table] m
INNER JOIN
(
SELECT [Counterpartyborrower],[Trade_type], [Account],SUM([MarketValueBaseCurrency]) as tempmarketvalue1
FROM [dbo].[BNYUKGSL113Table]
WHERE ( [Trade_type]='Loan')
GROUP BY [Counterpartyborrower],[Trade_type], [Account]
) f1 ON m.[Counterpartyborrower] = f1.[Counterpartyborrower] AND m.[Trade_type] = f1.[Trade_type] AND m.[Account] = f1.[Account]
INNER JOIN
(
SELECT [Counterpartyborrower],[Trade_type],SUM([MarketValueBaseCurrency]) as tempmarketvalue2
FROM [dbo].[BNYUKGSL113Table]
WHERE ( [Trade_type]='Loan')
GROUP BY [Counterpartyborrower],[Trade_type]
) f2 ON m.[Counterpartyborrower] = f2.[Counterpartyborrower] AND m.[Trade_type] = f2.[Trade_type]

1 Like
5

thank you vey much for the help. that worked as i wanted it to