Please Help - I need to get the average price and remaining quantity / shares

[darksystem]

Hi,

I have this data in the picture and want
to get the remaining quantity of the average of the price column.

This is the transaction / order made.

bought these 3 stocks @

dnl: 19.15 / share (bought 100 shares)
dnl: 19 / share (bought 100 shares)
dnl: 19.22 / share (bought 200 shares)

average price: 19.123333 / share
current share: 400

then I sold: 200 shares

remaining shares: 200
current average price: 19.123 / share

bought dnl again

dnl: 19.68 / share
shares bought: 100

DNL remaining shares: 300 shares

DNL average price should be: 19.40 / share or correct me if im wrong.

I have this sql query to get the remaining shares and average price

select distinct stock, sum(qty) as remainingshares, avg(price) as averageprice from game_orders group by stock;

but it gives me wrong result of the remaining shares and the average price

Any help is highly appreciated.

Sincerely

[swampBoogie]

When calculating remaining shares you should add bought shares and subtract sold shares. This can be accomplished by using a case expression in the sum as

sum(case when ordertype = 'sell' then -qty else qty end) as remainingshares

When calculating the average price you must consider the quantity. So, instead of using avg directly it needs to be somwhat like this

sum(price*qty)/sum(qty) as averageprice

The distinct qualifier will not make any difference and can be removed