Is there a way to display the count of selected rows as an additional column in a select statement?

Is there a way to display the count of selected rows as an additional column in a select statement?
I am listing list the past transactions for each SymbolID up to a maximum of 8.
I would also like to display an additional column that shows the total number of transactions
for each SymbolID. The total needs to be displayed in each detail row.

SELECT Top 100 SymbolID,ClosePrice,QuoteDate FROM StockHist s
WHERE SymbolID < 24000 AND QuoteDate <='07/20/2007' and
StockHistID in (select top 8 StockHistID from StockHist where symbolid=s.symbolid and quotedate<='7/20/07' order by symbolid,quotedate desc)
Order By SymbolID,QuoteDate Desc

Returns:    Totals row is not returned.
SymbolID  ClosePrice  QuoteDate  (Totals I would like to add a totals row as shown)
18368      14.90      2007-07-20      8
18368      15.35      2007-07-19      8
18368      15.26      2007-07-18      8
18368      14.96      2007-07-17      8
18368      17.37      2007-07-16      8
18368      17.46      2007-07-13      8
18368      17.34      2007-07-11      8
18368      17.24      2007-07-10      8
18369      3.79      2007-07-20        7      
18369      3.90      2007-07-19        7
18369      3.93      2007-07-18        7
18369      4.02      2007-07-17        7
18369      4.04      2007-07-16        7
18369      4.02      2007-07-13        7
18369      4.09      2007-07-11        7
DovbermanAsked:
Who is Participating?
 
peter57rCommented:
Can you do this ?

SELECT Top 100 SymbolID,ClosePrice,QuoteDate, Histcount.knt
FROM StockHist s
inner join
(Select symbolid, count(*) as Knt from stockhist  WHERE SymbolID < 24000 AND QuoteDate <='07/20/2007' group by symbolId) as HistCount
on s.symbolid = histcount.symbolid
WHERE SymbolID < 24000 AND QuoteDate <='07/20/2007' and
StockHistID in (select top 8 StockHistID from StockHist where symbolid=s.symbolid and quotedate<='7/20/07' order by symbolid,quotedate desc)
Order By SymbolID,QuoteDate Desc
0
 
peter57rCommented:
(SElect count(*) from orders  as C where c.customerid= orders.customerid)

SELECT Top 100 SymbolID,ClosePrice,QuoteDate,
(SElect count(*) from stockhist as C where c.symbolid= stockhist.symbolid) as HistCount
FROM StockHist s
WHERE SymbolID < 24000 AND QuoteDate <='07/20/2007' and
StockHistID in (select top 8 StockHistID from StockHist where symbolid=s.symbolid and quotedate<='7/20/07' order by symbolid,quotedate desc)
Order By SymbolID,QuoteDate Desc
0
 
peter57rCommented:
Sorry - ignore the first line - i was just using it as a model.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
DovbermanAuthor Commented:
I tried it with the following results:

SELECT Top 100 SymbolID,ClosePrice,QuoteDate,
(SElect count(*) from stockhist as C where c.symbolid= stockhist.symbolid) as HistCount
FROM StockHist s
WHERE SymbolID < 24000 AND QuoteDate <='07/20/2007' and
StockHistID in (select top 8 StockHistID from StockHist where symbolid=s.symbolid and quotedate<='7/20/07' order by symbolid,quotedate desc)
Order By SymbolID,QuoteDate Desc

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "stockhist.symbolid" could not be bound.
0
 
peter57rCommented:
I may not be seing this Q in the same zone that you are.
I have answered this as part of the MS-Access forum.
Clearly you are using a different product.


0
 
DovbermanAuthor Commented:
I am using SQL Server 2005.

I understand that In Access you can refer to another query and join the two.

That's why I was surprised that your suggestion did not work.
0
 
DovbermanAuthor Commented:
the following error occurs:

Msg 209, Level 16, State 1, Line 6
Ambiguous column name 'SymbolID'.
Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'SymbolID'.

I will build a second dataset named dstSymbolCount that has a SymboID and a Count Column.
Then I will loop through the dstSymbolCount  dataset and find the SymbolID in the other dataset.
Then I will use the count from dstSymbolCount in my logic.

Thanks for the help.

Let me know if there is a way to display the count as a column in the main dataset.

Thanks,
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.