Need help with MAX or COUNT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need help with MAX or COUNT

Author

Topic

toddhd
Yak Posting Veteran

55 Posts

Posted - 2008-07-30 : 15:40:09

I'm sure this is one of those cases where I'm just too close to the forest to see the trees, and I need help. I have the following SPROC (modified for brevity). Basically, it returns paged data from a table. It works fine. What I want to be able to do is add a MAX() or COUNT() column to the returned data so that I have an idea of how many total rows are in the table. But I keep getting an error about COUNT() not being in an aggregate function or GROUP BY. I keep trying various syntax's to do this, but I can't get it working. How do I return the total number of records in the table?

ALTER Procedure dbo.usp_SelectImportByAdminPaged
@aid int,
@PageNum int,
@PageSize int
AS
WITH Claims AS
(
SELECT ROW_NUMBER() OVER(ORDER BY i.import_dt DESC) AS RowNum,
-- COUNT(i.import_id) as TotalRecords, --This is what I want to do, and can't
-- MAX(RowNum) might work too?
i.import_id,
i.import_dt
-- yada yada, tons of column names here
FROM HealthCareClaimImport i
WHERE i.admin_id = @aid
)
SELECT *
FROM Claims
WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1
AND @PageNum * @PageSize
ORDER BY import_dt DESC

-Todd Davis

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-30 : 15:47:56

Just use @@ROWCOUNT. You could either put it in an output variable, return an extra result set, or use RETURN. I'd go with an output variable.

@rc int OUTPUT
...
...
SET @rc = @@ROWCOUNT

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-30 : 15:48:29

If you want just a rowcount for a table though, just use SELECT COUNT(*) FROM TableName.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

toddhd
Yak Posting Veteran

55 Posts

Posted - 2008-07-30 : 16:01:03

quote:
Originally posted by tkizer

If you want just a rowcount for a table though, just use SELECT COUNT(*) FROM TableName.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

I added the following to the end of the SProc (I know I'm not returning anything yet):

DECLARE @rc int
SET @rc = (SELECT COUNT(*) FROM Claims)

When I run the SProc, I get an error:
Invalid object name 'Claims'

Does Claims go out of scope or something like that?

-Todd Davis

toddhd
Yak Posting Veteran

55 Posts

Posted - 2008-07-30 : 16:13:19

Ah... I finally got it...

SELECT ROW_NUMBER() OVER(ORDER BY i.import_dt DESC) AS RowNum,
TotalRows=Count(*) OVER(),
i.import_id,

-Todd Davis

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources