#1
  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

IMN logo majestic logo threadwatch logo seochat tools logo