0

I am developing a Stocks management application, the Portfolio register table has 4 columns, other than date and registry_key, and it looks like this:

----------------------------------------
| stock_key | user_key | units | price |
----------------------------------------
| fund_a    | user_1   | 1000  | 100.35|
| fund_c    | user_2   | 4000  | 101.55|
| fund_d    | user_3   | 2000  | 105.65|
| fund_a    | user_1   | 1500  | 101.45|
| fund_b    | user_1   | 500   | 103.35|
----------------------------------------

I wish to select the portfolio of user_1 with distinct stock_key. Plus I need the price as the aggregate total paid over the number of units. For example:

For user_1 the result should be:

[ 
    {fund_key: "fund_a", units: sum(1000,1500), price: sum(1000*100.35,1500*101.45)/sum(1000,1500)},
    {fund_key: "fund_b", units: 500, price: 103.35}
]

This is important because once the user has brought more units of the same stock at a lower/higher price than before, the accumulated stocks price is averaged out.

1

Here's your query:

    SELECT 
        concat("fund_key: ",stock_key,", units: ",sum(units),", price: ",round(sum(units*price)/sum(units),2)) as result
    FROM PRUEBA.new_table2
    GROUP BY stock_key,user_key;

Result:

enter image description here

1

Here, this might help

SELECT 
     stock_key "FUND_KEY",
     SUM(units) "UNITS",
     SUM(units * price) / SUM(units) "PRICE"
FROM
     stock_mngmt_app
WHERE
     USER_KEY = 'user_1'
GROUP BY STOCK_KEY
  • You can remove USER_KEY from the GROUP BY clause. – Lennart Jun 28 '15 at 7:07
  • @Lennart yes and that would be less costly as well. Thanks btw. – Janardhan Maithil Jun 28 '15 at 9:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.