Val K Val K - 3 years ago 117
SQL Question

Getting grand totals with grouping sets

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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download