How to get a Grand Total in SQL Server without Subtotals using ROLLUP

I’ve written about how to generate Grand Totals in SQL before, and one of the methods was using the ROLLUP function.

This time I had multiple columns of data to display. Using the ROLLUP function, I was getting subtotals for the other columns.

In my SQL Server query, I only wanted the Grand Total, and not show the Subtotals.

“ROLLUP” may not be available in all versions of SQL Server.

Data without Subtotals

Let’s suppose you have this SQL:

-- Original; no totals / subtotals
SELECT 
    planet
    , agent
    , SUM(total_hours) AS totalHours
FROM myTable
GROUP BY 
    planet
    , agent

It’ll give you this:

Basic “SELECT” statement

Adding Totals using WITH ROLLUP

If you have multiple columns, adding WITH ROLLUP will total every column. In essence it will give you Totals and Subtotals.

The Totals/Subtotals will have a NULL value, so I replaced that with appropriate text in the SELECT clause.

I also added an ORDER BY clause to sort the data.

-- Add "WITH ROLLUP"
-- will give Totals *and* Subtotals
-- also updated the SELECT clause
-- and added an ORDER BY
SELECT 
    ISNULL(planet, 'Total') AS planet
    , ISNULL(agent, 'Subtotal') AS agent
    , SUM(total_hours) AS totalHours
FROM myTable
GROUP BY 
    planet
    , agent
WITH ROLLUP
ORDER BY
    CASE WHEN planet IS NULL THEN 1 ELSE 0 END
    , planet
    , CASE WHEN agent IS NULL THEN 1 ELSE 0 END
    , agent

You now have:

SQL Server query adding “WITH ROLLUP”

How to remove Subtotals but keep the Grand Total

The trick was to add HAVING GROUPING (columnName) = [value].

Take a look at the last 3 lines of code:

SELECT column1, column2, SUM(value) 
FROM table 
GROUP BY column1, column2 
WITH ROLLUP 
HAVING 
      GROUPING(column1) = 1 
  OR GROUPING(column2) = 0

The HAVING clause filters for subtotal/grand total rows. The GROUPING function will return 1 for a subtotal row and 0 for non-subtotal rows.

Since I wanted to display the Grand Total for the first column, but not for the second column, use this syntax:

HAVING 
      GROUPING(column1) = 1 
  OR GROUPING(column2) = 0

Final SQL:

-- Only show Grand Totals
-- Don't show Subtotals
SELECT 
    ISNULL(planet, 'Total') AS planet
    , agent
    , SUM(total_hours) AS totalHours
FROM myTable
GROUP BY 
    planet
    , agent
WITH ROLLUP
HAVING
      GROUPING(planet) = 1
  OR GROUPING(agent) = 0
ORDER BY
    CASE WHEN planet IS NULL THEN 1 ELSE 0 END
    , planet
    , CASE WHEN agent IS NULL THEN 1 ELSE 0 END
    , agent

Final Output:

SQL Server results — totals without a subtotal