Get total count using a window function

Question

Hey all this is the query I have so far:

WITH LIMIT AS
        (SELECT 
             U.userID
            ,U.username
            ,U.fname    
            ,U.mname    
            ,U.lname    
            ,U.email    
            ,U.active 
            ,S.sName
            ,S.sID
            ,T.[value]
            ,T.trackingNumberID
            ,SU.primaryLocation
            ,row_number() OVER (ORDER BY U.userid) AS RN 
            ,COUNT(*) OVER (ORDER BY U.userid) AS CNT 
            ,UR.roleID
        FROM 
            [---].[dbo].[tblUsers]                          AS U
        LEFT OUTER JOIN [---].[dbo].[tblTrackingNumbers]    AS T
            ON T.userID = U.userID
        LEFT OUTER JOIN [---].[dbo].[tblSU]                 AS SU
            ON U.userID = SU.userID
        LEFT OUTER JOIN [---].[dbo].[tblS]                  AS S
            ON SU.sID = S.sID 
        LEFT OUTER JOIN [---].[dbo].[tblUserRoles]          AS UR 
            ON UR.userID = U.userID
        LEFT OUTER JOIN [---].[dbo].[tblRoles]              AS R 
            ON UR.roleID = R.roleID
        WHERE 
            U.active = 1 
        AND 
            SU.primaryLocation = 1
        AND 
            SU.active = 1 
        AND 
                U.orgID = 1
            AND 
                S.ID = 35 
            AND U.userID IN (SELECT userID 
                              FROM [---].[dbo].[tblSU] AS SU 
                              INNER JOIN [].[dbo].[tblS] AS S 
                                 ON S.sID = SU.sID 
                              WHERE 
                                 SU.active = 1 
                              AND 
                                 S.sID = 35)
) SELECT * FROM LIMIT WHERE RN Between 0 AND 10000

As you can see by the query above I am trying COUNT(*) OVER (ORDER BY U.userid) AS CNT which gives me the same count as RN.

What I need is the total amount of records this would be bringing back (842 rows).

Answer 1

COUNT(*) OVER (ORDER BY U.userid) AS CNT calulates a "running count" - the count until "that" row. If you want to count all rows in the complete result, use the window function without the order by

COUNT(*) OVER () AS CNT

Answer 2

this might sound cuckoo, but i found with large tables you get better performance if you select the count into a variable and then select your records and just add the variable. something with the count(*) over() causes bad performance when tables get too large.

DECLARE @RecordCount INT
SELECT  @RecordCount = COUNT(*)
FROM    [---].[dbo].[tblUsers] AS U
        LEFT OUTER JOIN [---].[dbo].[tblTrackingNumbers] AS T ON T.userID = U.userID
        LEFT OUTER JOIN [---].[dbo].[tblSU] AS SU ON U.userID = SU.userID
        LEFT OUTER JOIN [---].[dbo].[tblS] AS S ON SU.sID = S.sID
        LEFT OUTER JOIN [---].[dbo].[tblUserRoles] AS UR ON UR.userID = U.userID
        LEFT OUTER JOIN [---].[dbo].[tblRoles] AS R ON UR.roleID = R.roleID
WHERE   U.active = 1
        AND SU.primaryLocation = 1
        AND SU.active = 1
        AND U.orgID = 1
        AND S.ID = 35
        AND U.userID IN (SELECT userID
                         FROM   [---].[dbo].[tblSU] AS SU
                                INNER JOIN [].[dbo].[tblS] AS S ON S.sID = SU.sID
                         WHERE  SU.active = 1
                                AND S.sID = 35)

SELECT  U.userID,
        U.username,
        U.fname,
        U.mname,
        U.lname,
        U.email,
        U.active,
        S.sName,
        S.sID,
        T.[value],
        T.trackingNumberID,
        SU.primaryLocation,
        @RecordCount AS CNT,
        UR.roleID
FROM    [---].[dbo].[tblUsers] AS U
        LEFT OUTER JOIN [---].[dbo].[tblTrackingNumbers] AS T ON T.userID = U.userID
        LEFT OUTER JOIN [---].[dbo].[tblSU] AS SU ON U.userID = SU.userID
        LEFT OUTER JOIN [---].[dbo].[tblS] AS S ON SU.sID = S.sID
        LEFT OUTER JOIN [---].[dbo].[tblUserRoles] AS UR ON UR.userID = U.userID
        LEFT OUTER JOIN [---].[dbo].[tblRoles] AS R ON UR.roleID = R.roleID
WHERE   U.active = 1
        AND SU.primaryLocation = 1
        AND SU.active = 1
        AND U.orgID = 1
        AND S.ID = 35
        AND U.userID IN (SELECT userID
                         FROM   [---].[dbo].[tblSU] AS SU
                                INNER JOIN [].[dbo].[tblS] AS S ON S.sID = SU.sID
                         WHERE  SU.active = 1
                                AND S.sID = 35)
ORDER BY U.userID
OFFSET 0 ROWS FETCH NEXT 10000 ROWS ONLY