StealthRT StealthRT - 2 months ago 6
SQL Question

Get total count using a window function

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

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
Comments