sqlsql-servert-sqlrollupsql-server-2019

How to calculate Subtotals in SQL Queries

I can’t calculate subtotal and total using GROUP BY ROLLUP syntax in a SQL Server 2019 database:

CREATE TABLE [dbo].[G_Dashboard] 
(
    [orderId] int  NULL,
    [C_CO] varchar(4) NULL,
    [D_CO] varchar(50) NULL,
    [Total] int  NULL,
    [A_RG] int  NULL,
    [B_RG] int NULL,
    [C_RG] int  NULL,
    [D_RG] int  NULL,
    [E_RG] int  NULL,
    [DateHour] datetime  NULL,
)

INSERT INTO [dbo].[G_Dashboard] ([C_CO], [D_CO], [Total], [A_RG], [B_RG], [C_RG], [D_RG], [E_RG], [DateHour], [orderId]) 
VALUES (N'710', N'CGL', N'4', N'3', N'0', N'1', N'0', N'0', N'2025-05-26 17:51:00.000', N'1')

INSERT INTO [dbo].[G_Dashboard] ([C_CO], [D_CO], [Total], [A_RG], [B_RG], [C_RG], [D_RG], [E_RG], [DateHour], [orderId]) 
VALUES (N'810', N'PLR', N'8', N'8', N'0', N'0', N'0', N'0', N'2025-05-26 17:51:00.000', N'2')

INSERT INTO [dbo].[G_Dashboard] ([C_CO], [D_CO], [Total], [A_RG], [B_RG], [C_RG], [D_RG], [E_RG], [DateHour], [orderId]) 
VALUES (N'830', N'CTN', N'10', N'10', N'0', N'0', N'0', N'0', N'2025-05-26 17:51:00.000', N'2')

INSERT INTO [dbo].[G_Dashboard] ([C_CO], [D_CO], [Total], [A_RG], [B_RG], [C_RG], [D_RG], [E_RG], [DateHour], [orderId]) 
VALUES (N'E10', N'BLG', N'2', N'1', N'0', N'1', N'0', N'0', N'2025-05-26 17:51:00.000', N'3')

INSERT INTO [dbo].[G_Dashboard] ([C_CO], [D_CO], [Total], [A_RG], [B_RG], [C_RG], [D_RG], [E_RG], [DateHour], [orderId]) 
VALUES (N'E40', N'MDN', N'2', N'1', N'0', N'1', N'0', N'0', N'2025-05-26 17:51:00.000', N'3')

I need this result set returned:

C C_CO orderid SalesTotal
7 710 1 4
7 7 subtotal 4
8 810 2 8
8 830 2 10
8 8 subtotal 18
E E10 3 2
E E40 3 2
E E subtotal 4
total 26

Instead I get this: DB FIDDLE

SELECT 
    LEFT(C_CO, 1) C,
    C_CO,
    orderid,
    SUM(Total) AS SalesTotal 
FROM
    [dbo].[G_Dashboard] 
GROUP BY
    C_CO,
    ROLLUP(LEFT(C_CO, 1), orderid);

How do I resolve this problem?

test

Solution

  • I would use GROUPING SETS for this. You can then use the GROUPING function to see if the value is being grouped on, to adjust the value for the orderid column, and the order of your results. For ease, I also move the expression for C into a CROSS APPLY.

    This then results in the following query:

    SELECT V.C AS C,
       ISNULL(C_CO,V.C) AS C_CO,
       CASE WHEN GROUPING(G.C_CO) = 0 THEN CONVERT(varchar(20),G.orderId)
            WHEN GROUPING(V.C) = 0 THEN 'SubTotal'
            ELSE 'Total'
       END AS Orderid,
       SUM(G.Total) AS SalesTotal       
FROM [dbo].[G_Dashboard] G
     CROSS APPLY(VALUES(LEFT(G.C_CO, 1)))V(C)
GROUP BY GROUPING SETS((G.C_CO, G.orderId, V.C), --Effectively not aggregated, but would be one row per orderid and C_CO
                       (V.C), --Grouped at prefix level; the SubTotal
                       ()) --No columns are grouped on, the Total
ORDER BY GROUPING(V.C),
         V.C;

    db<>fiddle