Getting grand totals with grouping sets

Question

I am migrating SQL SERVER 2005 to SQL SERVER 2014 and came across this problem. I have a "search engine" STORED PROCEDURE that builds report queries on the fly for my reporting website. In the old version I used COMPUTE BY to get sub totals and grand totals. COMPUTE BY is no longer supported in SQL SERVER 2014 and I switched to using GROUPING SETS. However, there is a problem. The reports can have more than one page. They can have upwards 200 pages. I only want to show grand total on the last page. By default ever page is fetching 1000 records. The last page would usually show the remaining records and the grand total over the whole record set. I could achieve that with COMPUTE BY. With GROUPING SETS, I cannot. I can only calculate grand total over the last page only, not the entire record set. Any suggestions would be appreciated. Thanks.

Answer 1

You should be able to just use WITH ROLLUP and page with OFFSET/FETCH;

CREATE TABLE test (id INT, val INT);

INSERT INTO test VALUES (1,1),(1,2),(1,3),(1,4),(2,1),(2,2),(2,3),(2,4),(2,5);
INSERT INTO test VALUES (3,1),(3,2),(3,3),(3,4),(4,1),(4,2),(4,3),(4,4),(4,5);

Whole result;

SELECT id, SUM(val) val FROM test GROUP BY id WITH ROLLUP
ORDER BY CASE WHEN id IS NULL THEN 1 END, id 

id    val
 1     10
 2     15
 3     10
 4     15
 null  50  -- Total sum rollup

...and the last page of page size 3;

SELECT id, SUM(val) val FROM test GROUP BY id WITH ROLLUP
ORDER BY CASE WHEN id IS NULL THEN 1 END, id 
OFFSET 3 ROWS -- skip 3 rows
FETCH NEXT 5 ROWS ONLY; -- take 3 rows

id    val
 4     15
 null  50  -- Total sum rollup

As you see, WITH ROLLUP + OFFSET/FETCH computes over the whole data set, not just the page.