### Thread: Use select result for formula

1. No Profile Picture
Registered User
Devshed Newbie (0 - 499 posts)

Join Date
Mar 2013
Posts
3
Rep Power
0

#### Use formula in query

How do I use the family portfolio total from my 3rd SELECT as the 'denominator' for the formula in the family wt column in my first and second SELECT? .

Sample Output (spacing added here for clarity)

Ticker Family Holder Account Units Value Family Wt
------ -------- ------- --------- ------ ------ -----------
GE F1 H1 A1 5 125 0.25% <-- help insert denominator for this formula
GE F1 H1 A2 7 175 0.35%
GE F1 H2 A3 4 100 0.20%
GE F1 H2 A4 9 225 0.45%
GE F1 (family GE total) 25 625 1.25%

GE F2 H3 ...
GE F2 H3
GE F2 H4

BA F1 ...
BA F1
BA F1
BA
BA
BA
BA
...
(family portfolio totals at bottom of table)
F1 50,000 <--list of denominators to be used for formula
F2 99,000
F3 390,000
F4 150,000

So in plain English: there are 5 shares of GE, for a total value of \$125 in Account A1, which is one of Family F1's accounts. That \$125 position represents 0.25% of the total wealth of Family F1, which is \$50,000, as shown at the bottom of the table.

Query:

# position in each account
SELECT securities.ticker, accountholders.family, accounts.accountholder AS holder, positions.account, positions.units, round(securities.currentprice*positions.units,0) AS value, round(securities.currentprice*positions.units,0)/'denominator' AS family_wt
FROM positions
JOIN accounts ON accounts.accountnumber=positions.account
JOIN accountholders ON accountholders.accountholder=accounts.accountholder
JOIN families ON families.family=accountholders.family
JOIN securities ON securities.ticker=positions.ticker

UNION

# sum of positions in a specific ticker for all accounts of a given family
SELECT securities.ticker, accountholders.family, null, null, sum(positions.units), sum(round(securities.currentprice*positions.units,0)), sum(round(securities.currentprice*positions.units,0))/'denominator'
FROM positions
JOIN accounts ON accounts.accountnumber=positions.account
JOIN accountholders ON accountholders.accountholder=accounts.accountholder
JOIN families ON families.family=accountholders.family
JOIN securities ON securities.ticker=positions.ticker
GROUP BY ticker, family

UNION

# sum of each family's total portfolio value to be used as denominator
SELECT null, accountholders.family, null, '', '', sum(round(securities.currentprice*positions.units,0)), ''
FROM positions
JOIN accounts ON accounts.accountnumber=positions.account
JOIN accountholders ON accountholders.accountholder=accounts.accountholder
JOIN families ON families.family=accountholders.family
JOIN securities ON securities.ticker=positions.ticker
GROUP BY family

ORDER BY coalesce(ticker,'zzzz'), family, coalesce(holder,'zzzz')
Last edited by Finance Newbie; March 28th, 2013 at 04:32 PM. Reason: Improved explanation