How to calculate Subtotals in SQL Queries using SQL Server 2019

Databases
1

Hello everyone, I need your help.

I can’t calculate subtotal and total using GROUP BY ROLLUP syntax in DB SQL Server 2019.

I need this return

|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 have this DB FIDDLE

Can you help me?

Thanks in advance for any help

2

weird output requirements

this is as close as i got –

SELECT CASE WHEN C_CO IS NULL
            THEN ' '
            ELSE LEFT(C_CO,1) END  AS C
     , CASE WHEN C_CO IS NULL
            THEN ' '
            WHEN orderid IS NULL
            THEN LEFT(C_CO,1)
            ELSE C_CO END  AS C_CO
     , CASE WHEN C_CO IS NULL
            THEN 'total'
            WHEN orderid IS NULL
            THEN 'subtotal'
            ELSE CAST(orderid AS VARCHAR) END AS orderid
     , SUM(Total) AS SalesTotal 
  FROM [dbo].[G_Dashboard] 
GROUP 
    BY ROLLUP( LEFT(C_CO,1) , C_CO, orderid )
2 Likes