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

Question

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.

Answer 1

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 ;

Answer 2

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