Need Record 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 Record Count

Author

Topic

ronnieoverby
Starting Member

36 Posts

Posted - 2008-12-09 : 14:40:02

This seems like it should be simple. But I'm still new at this.

Here is some sql that is generated dynamically by my stored procedure:

WITH OrderedRoster AS
            (
                SELECT *, ROW_NUMBER() OVER (ORDER BY ColleagueID) AS RowNumber
                FROM [Roster].[ViewRoster]
                WHERE 1 = 1  ) 
            SELECT *
            FROM OrderedRoster 
            WHERE (RowNumber BETWEEN @First AND @Last)

How can I get the record count of OrderedRoster?

Thanks!

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-12-09 : 14:50:35

max(rownumber)

ronnieoverby
Starting Member

36 Posts

Posted - 2008-12-09 : 14:55:05

Where is that executed?

ronnieoverby
Starting Member

36 Posts

Posted - 2008-12-09 : 15:16:14

I did this instead:

WITH OrderedRoster AS
            (
                SELECT *, ROW_NUMBER() OVER (ORDER BY ColleagueID) AS RowNumber, COUNT(*) OVER(PARTITION BY NULL) AS TotalRowCount
                FROM [Roster].[ViewRoster]
                WHERE 1 = 1  ) 
            SELECT *
            FROM OrderedRoster 
            WHERE (RowNumber BETWEEN @First AND @Last)

This sucks though because now I have a column in my result set with every single record having the same value. How can I store this into a variable?

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-12-09 : 16:16:32

Why do you need COUNT(*). MAX(RowNumber) will give you the count.


declare @rownumber int

;WITH OrderedRoster AS
            (
                SELECT *, ROW_NUMBER() OVER (ORDER BY ColleagueID) AS RowNumber
                FROM [Roster].[ViewRoster]
                WHERE 1 = 1  ) 
            SELECT *, @rownumber=(select max(rownumber) from orderedroster) as recordcount
            FROM OrderedRoster 
            WHERE (RowNumber BETWEEN @First AND @Last)

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-12-09 : 16:24:17

Can you explain a little more. To get the record count of the table is simply


select count(*)
FROM [Roster].[ViewRoster] a

If you are looking for the totals for a specific group such as total by ColleagueID it's



select count(*) as ColleagueCount,ColleagueID
FROM [Roster].[ViewRoster] a
group by a.ColleagueID

If you want to go a step farther and link the counts with the records it's simply


Select a.*,b.ColleagueCount 
FROM [Roster].[ViewRoster] a
Inner Join
(
select count(*) as ColleagueCount,ColleagueID
FROM [Roster].[ViewRoster] a
group by a.ColleagueID
) b
on a.ColleagueID = b.ColleageID

Please explain more if this is not what you are looking for.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources