MySQL Forums
Forum List  »  Newbie

Could anyone help me to write a query to do the following:
Posted by: craig baines
Date: October 06, 2015 03:49AM

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!!

Options: ReplyQuote


Subject
Written By
Posted
Could anyone help me to write a query to do the following:
October 06, 2015 03:49AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.