• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 234
  • Last Modified:

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
0
Dovberman
Asked:
Dovberman
  • 4
  • 3
1 Solution
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now