SQL query - How to select to display group, group total, grand total?
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 ║
╚═══════════════╩════════════════════════╩══════════════════╝