Power By 专否社区

Current trade profit/loss sql query

Go To StackoverFlow.com

0

I'm trying my hand at a couple of tester database interview questions; coming from a client side background, I'm doing a few to get up to scratch, and I'm currently struggling.

I have a portfolio table with 'TradeID', 'Ticker', 'Price', and 'Volume'. Currently 3 rows in the table, 2 buys of 10,000 and 1 sell of 10,000, all of the same company. I basically need to work out the daily portfolio value given a price. So if the current price is 1.00, the value total would be 10,000.

I've tried every subquery possible, but cannot for the life of me get it working, I end up with a aggregate or group by error, any help would be appreciated. Apologies if I've missed anything.

Edit: I'm trying to create a query like this:

select
 sum
((sum(trade.volume) * 1.0) -
  (sum(trade.volume * trade.price)))

from trade

Edit2: test data I currently have & making example easier:

1.0 is the price at close. I have 3 trades; trade.volume for the three would be +10,000, +10,000, -10,000 - all the same company trade.price : 1.1 - is the price the stock was bought at

so with sum((trade.volume) *1) I'm trying to get 10,000 as the current value from open volumes (sum(trade.volume * trade.price)) would be 11,000 as purchase price

2012-04-04 23:45
by DBAWantToBe
a SUM() and a case statement to determine which rows are buys versus sells are sufficien - Mitch Wheat 2012-04-04 23:48
Hi Mitch, I've not taken the case route; cheers, I'll take a look - DBAWantToBe 2012-04-04 23:49
Can you post some sample data.. - Teja 2012-04-05 00:00

0

If the sell is negative then 

Select ticket, sum(volume*trade) from table group by ticket
2012-04-05 01:00
by paparazzo

0

I think this is what you have:

CREATE TABLE Portfolio (
    TradeID int,
    Ticker varchar(10),    -- the company identifer
    Price money,
    Volume int,    -- probably not actually an int
)

with data:

INSERT Portfolio VALUES ( 1, 'ACME', 1.1, 10000) -- a buy
INSERT Portfolio VALUES ( 2, 'ACME', 1.1, 10000) -- a buy
INSERT Portfolio VALUES ( 3, 'ACME', 1.1, -10000) -- a sell

Given

I basically need to work out the daily portfolio value given a price. So if the current price is 1.00, the value total would be 10,000.

I think you want the current net Volume, multiplied by today's price:

DECLARE @priceNow money
SET @priceNow = 1.0

DECLARE @currentValue money
SELECT @currentValue = @priceNow * SUM(Volume) FROM Portfolio WHERE Ticker = 'ACME'

Note that the transaction price is irrelevant to knowing the currently-owned value.

2012-04-05 08:08
by AakashM
Ads