Problem with Sub-Query

[JapanFreak]

Hi there,

I have a problem with an SQL query related to a table which contains stock market transactions. The query requests data from a table called tblTransactions with (among others) columns Date, Ticker, Quantity and Price.

By adding up the quantities bought (positive number) and sold (negative number) I can determine the number of stocks I hold at a certain date for each ticker.

What I am looking for is the date, when a position in a certain stock identified by a ticker (I use the example of GT) is sold completely, i.e. the quantity is zero again.

The following code is my draft, but unfortunately it does not work because Access obviously does not allow to use an aggregate function like sum(Quantity) in the WHERE part of a sub-query.

SELECT T.Ticker, (SELECT Top 1 [Date] FROM [tblTransactions] WHERE sum(T.Quantity)=0 ORDER BY [Date] DESC) AS SellDate
FROM tblTransactions AS T
WHERE T.Ticker="GT"
GROUP BY T.Ticker;

How do I have to deal with problems like this? I am happy for any hint that could lead to a solution...

Thank you very much.

Best regards,
JapanFreak

 

[Moniker]

Use HAVING instead of WHERE. HAVING is used in aggregate queries. You'll also have to group by the other entries.

 

[JapanFreak]

Not 100% clear...

Hi,

thank you very much for your reply. I used now HAVING instead of WHERE, but Access still asks me to include sth in the GROUP BY part... but what? Date does not work, but what else??

SELECT T.Ticker, (SELECT Top 1 [Date] FROM [tblTransactions] HAVING sum(T.Quantity)=0 ORDER BY [Date] DESC) AS SellDate
FROM tblTransactions AS T
WHERE T.Ticker="GT"
GROUP BY T.Ticker, T.Date;

Best,
JapanFreak

 

[EMP]

Hopefully this is what you are after:

SELECT T.Ticker, (Select Top 1 [Date] from tblTransactions where [ticker]=t.[Ticker] Order By [Date] Desc) AS SellDate
FROM tblTransactions AS T
GROUP BY T.Ticker
HAVING Sum(T.Quantity)=0;

^

 

[JapanFreak]

Problem solved, one additional question

Thank you very much, this query works. However, I have one additional question.
How can I use fields, which I have created with a sub-query? That is, with the above query I have all traded Tickers with the date, when the position in each particular position became zero. How can I now find out the price for that last trade? Adding the sub-query

(SELECT [Price] FROM tblTransactions WHERE [Ticker]=T.[Ticker] AND [Date]=SellDate) AS Exit

does not work. What else do I have to do?

Best regards,
JapanFreak

 

[EMP]

SellDate is an alias. We can't use aliases of calculated fields in subqueries. You need to replace it with its original expression.

(SELECT [Price] FROM tblTransactions WHERE [Ticker]=T.[Ticker] AND [Date]=(Select Top 1 [Date] from tblTransactions where [ticker]=t.[Ticker] Order By [Date] Desc)) AS Exit

^