While Loop in TSQL with Sum totals
- by RPS
I have the following TSQL Statement, I am trying to figure out how I can keep getting the results (100 rows at a time), store them in a variable (as I will have to add the totals after each select) and continue to select in a while loop until no more records are found and then return the variable totals to the calling function.
SELECT [OrderUser].OrderUserId, ISNULL(SUM(total.FileSize), 0), ISNULL(SUM(total.CompressedFileSize), 0)
FROM
(
SELECT DISTINCT TOP(100) ProductSize.OrderUserId, ProductSize.FileInfoId,
CAST(ProductSize.FileSize AS BIGINT) AS FileSize,
CAST(ProductSize.CompressedFileSize AS BIGINT) AS CompressedFileSize
FROM ProductSize WITH (NOLOCK)
INNER JOIN [Version] ON ProductSize.VersionId = [Version].VersionId
) AS total RIGHT OUTER JOIN [OrderUser] WITH (NOLOCK) ON total.OrderUserId = [OrderUser].OrderUserId
WHERE NOT ([OrderUser].isCustomer = 1 AND [OrderUser].isEndOrderUser = 0 OR [OrderUser].isLocation = 1)
AND [OrderUser].OrderUserId = 1
GROUP BY [OrderUser].OrderUserId
