SQL query - How to select to display group, group total, grand total?

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.

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 ║
╚═══════════════╩════════════════════════╩══════════════════╝

StackExchange Database Administrators Q#198055, answer score: 5