2

I've seen some advice but want to know the best way to select a group, group total, grand total from a data table WITHOUT using a subquery or unnecessary join.

My initial thought was something like this:

select   product_family, 
         sum(widgets), 
         sum(widgets) over ()
from     table.widget
group by product_family

or the following:

select   product_family, 
         sum(widgets), 
         sum(widgets) over (partition by all_field)
from     table.widget
group by product_family

Obviously neither of these works. I know a partition can be of a higher order than the actual rows/ groups, but not how to partition by "all" other than leaving it blank as the first example. However, it breaks with the group by statement.

Best I can find is something like this:

select product_family, 
       family_sum, 
       sum(family_sum) over () as grand_sum 
from (
      select   product_family, sum(widgets)
      from     table.widget
      group by product_family
     ) as A

That still involves a subquery though, which is okay. I just feel like I'm missing an easy function here.

3

GROUP BY ROLLUP or - even better - GROUP BY GROUPING SETS is best if you want an additional row with the grand total.

If you want the grand total in every row - in another column - then your query needs a minor adjustment. You can use the aggregate SUM(widgets) in a window function:

select   product_family, 
         sum(widgets)              as total, 
         sum(sum(widgets)) over () as grand_total
from     table.widget
group by product_family ;
5

GROUP BY ROLLUP does what you want.

As an example:

SELECT o.schema_id
    , type_desc
    , [Count Of Objects] = COUNT(o.object_id)
FROM sys.objects o
GROUP BY ROLLUP (o.schema_id, o.type_desc)

This produces the following output:

╔═══════════╦════════════════════════╦══════════════════╗
║ schema_id ║       type_desc        ║ Count Of Objects ║
╠═══════════╬════════════════════════╬══════════════════╣
║ 1         ║ PRIMARY_KEY_CONSTRAINT ║                7 ║
║ 1         ║ SERVICE_QUEUE          ║                3 ║
║ 1         ║ USER_TABLE             ║                8 ║
║ 1         ║ NULL                   ║               18 ║
║ 4         ║ INTERNAL_TABLE         ║               16 ║
║ 4         ║ SYSTEM_TABLE           ║               72 ║
║ 4         ║ NULL                   ║               88 ║
║ NULL      ║ NULL                   ║              106 ║
╚═══════════╩════════════════════════╩══════════════════╝

The NULL shown in the first two columns represents the roll-up amounts.

To make it "pretty", you can use some substitutions for the NULL rolled-up values:

SELECT [Schema Name] = CASE 
        WHEN s.name IS NULL THEN 
            '[Grand Total]' 
        ELSE s.name 
        END
    , [Object Type] = CASE 
        WHEN o.type_desc IS NULL THEN 
            '[Total - ' + COALESCE(s.name, 'Overall') + ']' COLLATE SQL_Latin1_General_CP1_CI_AS 
        ELSE o.type_desc 
        END
    , [Count of Objects] = COUNT(o.object_id)
FROM sys.objects o
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
GROUP BY ROLLUP (s.name, o.type_desc);
╔═══════════════╦════════════════════════╦══════════════════╗
║  Schema Name  ║      Object Type       ║ Count of Objects ║
╠═══════════════╬════════════════════════╬══════════════════╣
║ dbo           ║ PRIMARY_KEY_CONSTRAINT ║                7 ║
║ dbo           ║ SERVICE_QUEUE          ║                3 ║
║ dbo           ║ USER_TABLE             ║                8 ║
║ dbo           ║ [Total - dbo]          ║               18 ║
║ sys           ║ INTERNAL_TABLE         ║               16 ║
║ sys           ║ SYSTEM_TABLE           ║               72 ║
║ sys           ║ [Total - sys]          ║               88 ║
║ [Grand Total] ║ [Total - Overall]      ║              106 ║
╚═══════════════╩════════════════════════╩══════════════════╝
  • 2
    GROUP BY GROUPING SETS() is the modern way (I don't remember if ROLLUP is deprecated, yet). – Aaron Bertrand Feb 15 '18 at 21:45
  • Looking at the docs page for GROUP BY doesn't show anything about ROLLUP being deprecated.. In fact, in the example for GROUPING SETS (...) it uses ROLLUP. I thought ROLLUP was the replacement for the deprecated COMPUTE clause? docs.microsoft.com/en-us/sql/relational-databases/… – Max Vernon Feb 16 '18 at 15:23
  • 1
    @MaxVernon there are several examples with ROLLUP, GROUPING SETS and both. GROUP BY ROLLUP (a,b) is a shortcut for GROUP BY GROUPING SETS ( (), (a), (a,b) ) – ypercubeᵀᴹ Feb 16 '18 at 15:36
  • 1
    shorthand is good. – Max Vernon Feb 16 '18 at 15:38
  • 2
    The deprecated pieces of syntax are GROUP BY ... WITH ROLLUP and GROUP BY ... WITH CUBE, which are SQL Server-specific. The ROLLUP () and CUBE () GROUP BY functions were introduced in SQL Server 2008 and they are part of the SQL standard. – Andriy M Feb 18 '18 at 16:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.