Could anyone help me to write a query to do the following:
Hello all, forgive my noobness here.
Basically, heres how my table sits:
ID | Resourceid | Action | AmountBought | AmountSold | Date_Added
The data is added as either a buy:
(A/I) | 123456789 | buy | 500 | 0 | 2015-10-10 12:15:53
or as a sell:
(A/I) | 123456789 | sell| 0 | 500 | 2015-10-10 12:15:53
What im trying to achieve......
Id like to query this table an get back an average "AmountBought" based on how many "actions" where "buys", id also like the same for "amountSold", and "sells" all from within the previous x hours.
So SUM (amountbought) where action = 'buy' within the last 24 hours , and divide by count(*) where action = 'buy' within the last 24 hours, then also SUM (amountsold) where action = 'sell' within the last 24 hours , and divide by count(*) where action = 'sell' within the last 24 hours,
also...GROUPED BY resourceid.
so i want back the resource id, its average buyprice, its average sell price.
What i then want to do is take the average buy price away from the average sell price, and order it in highest value first.
So im left with the resourceid, and the difference/profit based on average buy/sell costs, in the last 24 hours
I know this is a LOT, and its a little above my knowledge, is it even possible to do all this in one go?
An if not can anyone make a suggestion on how to o about this, assuming there may be thousands of resourceids even when grouped.
Thanks!!