How to use subtotal in SQL

Question

I have a table with the following data

 CODE  NAME   AMOUNT VAT  TOTALAMT FLAG
  10   test1   100    10    110     1
  08   test2    0     0      0      1
  05   test3   10     0      0      2
  03   test4    0     0      0      2

I need a query that gives the following result. (Total for FLAG)

 CODE  NAME   AMOUNT VAT  TOTALAMT FLAG
  10   test1   100    10    110      1
  08   test2    0     0      0       1
 NULL SUBTOTAL 100    10    110    NULL
  05   test3   10     0      0       2
  03   test4    0     0      0       2
 NULL SUBTOTAL  10    0     10     NULL
TOTAL          110    10    120    NULL

Answer 1

  SELECT Code,
         isNULL(NAME,'SUBTOTAL') AS NAME,
         SUM(Total) AS Total,
         SUM(Vat) AS Vat,
         SUM(TotalAmt) AS 
         TotalAmt,
         Flag 
   FROM TABLE GROUP BY ROLLUP(Flag,Code,Name); 

Answer 2

Use ROLLUP:

SELECT
    MAX(CODE) AS CODE,
    NAME,
    MAX(AMOUNT) AS AMOUNT,
    MAX(VAT) AS VAT,
    MAX(TOTALAMT) AS TOTALAMT,
    FLAG
FROM yourTable
GROUP BY
    FLAG, NAME
WITH ROLLUP;

Demo

Answer 3

try it

;with cte
as
(
 select
  CODE,
  NAME,
  AMOUNT,
  VAT,
  TOTALAMT,
  FLAG
  from t1

  union aLL

  select
  CODE=NULL,
  NAME='SUBTOTAL',
  AMOUNT = SUM(AMOUNT),
  VAT = SUM(VAT),
  TOTALAMT = SUM(TOTALAMT),
  FLAG
  from T1
  GROUP BY FLAG
)
SELECT
*
FROM CTE
ORDER BY FLAG,CODE DESC