Select opposing actions only

[udigold1]

I have a table with list of stocks, trading days and order type (buy\sell), And I only want to select just stocks that had both buy and sell orders:



this is the original database:

Ticker	Date	Order Type	Quantity
MSFT	26/5/15	Buy	500
AAPL	26/5/15	Buy	50
GOOG	26/5/15	Buy	10
GE	27/5/15	Sell	-100
AAPL	27/5/15	Sell	-20
BA	27/5/15	Buy	10
GE	28/5/15	Buy	50

I want my query to return:

Ticker	Date	Order Type	Quantity
AAPL	26/5/15	Buy	50
GE	27/5/15	Sell	-100
AAPL	27/5/15	Sell	-20
GE	28/5/15	Buy	50

How Do I do that?
Thanks

[CJ_London]

ideally you need a unique id field as well but from the data you supplied this should work - change table and field names to suit

SELECT MyTable.*
FROM myTableINNER JOIN myTable as Tmp
ON MyTable.Ticker=Tmp.Ticker
WHERE (myTable.OrderType='BUY' and Tmp.OrderType='SELL') OR (myTable.OrderType='SELL' and Tmp.OrderType='BUY')

[udigold1]

Hi,
Thanks, but when I run it, it gives me more than 120,000 lines (the database is only 17,000 lines),
how come?

thanks

[CJ_London]

quite possibly

if you have say 2 buys and 10 sells (total 12 records), then you will get 20 records where a buy has a sell, and 20 records where a sell has a buy - a total of 40 records

Sounds like you may need to clarify what it is you actually require

[udigold1]

Hi,
I want to get the same result as my example suggests.
In my example I had a total of 7 records and I want my query to return 4 records.
And if we go by your example, I want the query to return 12 results (2 buys, 10 sells) not 40...

thanks!

[CJ_London]

OK, try changing SELECT to SELECT DISTINCT

[hapm]

Here is a quite different approach using subqueries.

With having both sell and buy orders you mean sell/buy on the same date or on different dates too?

Here is it with Date:

SELECT * FROM MyTable INNER JOIN (SELECT Ticker, Date, COUNT([Order Type]) AS OrderTypeCount FROM (SELECT DISTINCT Ticker, Date, [Order Type] FROM MyTable)) AS OrderTypeCountByTickerAndDate ON MyTable.Ticker = OrderTypeCountByTickerAndDate.Ticker AND MyTable.Date = OrderTypeCountByTickerAndDate.Date WHERE OrderTypeCountByTickerAndDate.OrderTypeCount > 1

and here without:

SELECT * FROM MyTable INNER JOIN (SELECT Ticker, COUNT([Order  Type]) AS OrderTypeCount FROM (SELECT DISTINCT Ticker, [Order  Type] FROM MyTable)) AS OrderTypeCountByTicker ON MyTable.Ticker =  OrderTypeCountByTicker.Ticker WHERE  OrderTypeCountByTicker.OrderTypeCount > 1

You can split up the query by defining subqueries for the derived tables if you like. Makes it much easier to maintain and understand.

[udigold1]

Sorry for taking the time, but wanted to say thank you, it worked!