Query to return all rows and count

[permissiondenied]

Hi, I made this query which returns all rows matching the WHERE but I am having trouble adding a count of the returned rows using the COUNT function.

SELECT * FROM tblAds WHERE adClose > {ts '2002-12-30 12:39:00' } AND adTitle Like '%test%' ORDER BY adClose ASC

My database being used is SQLServer7

Any help appreciated.

Phil

 

[mwolf00]

You can use @@ROWCOUNT to get the number of rows (if you are using a stored procedure. Otherwise, you can add the count to each row returned if you use a GROUP BY clause....


SELECT count(*), field1, field2, field3
FROM myTable
WHERE field1 LIKE '%test%'
GROUP BY field1, field2, field3 Get the Best Answers! faq333-2924
Merry Christmas!
mikewolf@tst-us.com
[santa2]

 

[r937]

mike, each ad has a timestamp

there won't be anything to group on

phil, use rowcount like mike said

at worst, count the returned rows in your scripting language

alternatively, you can get the database to pass the count using a UNION query with the same WHERE clause, as in
Result set row count along with query results

http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid480948_tax285649,00.html

this technique comes in handy when you want sum() instead of count()

rudy

 

[tlbroadbent]

In SQL 2000, the following query will return the row count on each row of the result set. It should be quite efficient of the adClose column is indexed.

SELECT *,
RowCnt=
(Select Count(*) FROM tblAds
WHERE adClose > {ts '2002-12-30 12:39:00'}
AND adTitle Like '%test%')

FROM tblAds
WHERE adClose > {ts '2002-12-30 12:39:00'}
AND adTitle Like '%test%'
ORDER BY adClose ASC Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[permissiondenied]

Hi guys,
thanks for your help.

Converting the query to a stored procedure is possible but difficult for me as there are so many variables that can make up the query, cheers Wolf.

Terry your solution also looks good and I do have an index on that column.

r937 I have gone for your solution as it is the easiest and programing timewise fastest for me. The query is being invoked in asp so I have used..
rsAdsn.RecordCount
to return the rows in the recordset. Whilst I know this is not the most efficient method I figure I`ll waite and see if the performance drops over time and if so take a longer look at the other two options mentioned here.

Thanks again all for your help guys, I really appreciate it.

Phil