title: SQL SERVER T-SQL Calculate SubTotal and Total by group
body: <p>I am trying to add subtotal by group and total to a table. I've recreated the data using the following sample.</p>
  <pre><code>DECLARE @Sales TABLE(
          CustomerName  VARCHAR(20),
          LegalID VARCHAR(20),
          Employee VARCHAR(20),
          DocDate DATE,
          DocTotal Int,
          DueTotal Int
  )
  INSERT INTO @Sales SELECT 'Jhon Titor','12345', 'Employee1','2015-09-01',1000,200 
  INSERT INTO @Sales SELECT 'Jhon Titor','12345', 'Employee1','2015-08-20',500,100
  INSERT INTO @Sales SELECT 'Jhon Titor','12345', 'Employee1','2015-08-18',200,50 
  INSERT INTO @Sales SELECT 'Deli Armstrong','2345', 'Employee1','2015-09-17',2300,700
  INSERT INTO @Sales SELECT 'Deli Armstrong','2345', 'Employee1','2015-09-11',5000,1000
  INSERT INTO @Sales SELECT 'Ali Mezzu','6789', 'Employee1','2015-09-07',300,200
  </code></pre>
  <p>Selecting <code>@Sales</code></p>
  <p><a href="https://i.stack.imgur.com/2MEom.png" rel="noreferrer"><img src="https://i.stack.imgur.com/2MEom.png" alt="enter image description here" /></a></p>
  <p>I need to add the customer subtotal just below customer occurrences and total in the end row of table like this:</p>
  <p><a href="https://i.stack.imgur.com/W4QCV.png" rel="noreferrer"><img src="https://i.stack.imgur.com/W4QCV.png" alt="enter image description here" /></a></p>
  <p>what I've tried so far:</p>
  <pre><code>select 
      case 
          when GROUPING(CustomerName) = 1 and
               GROUPING(Employee) = 1 and 
               GROUPING(DocDate) = 1 and
               GROUPING(LegalID) = 0 then 'Total ' + CustomerName
  
          when GROUPING(CustomerName) = 1 and
               GROUPING(Employee) = 1 and
               GROUPING(DocDate) =1 and
               GROUPING(LegalID) = 1 then 'Total'
  
          else CustomerName end as CustomerName,
      LegalID, Employee,DocDate,
      sum(DocTotal) as DocTotal,
      sum(DueTotal) as DueTotal 
  From @Sales 
  group by LegalID, CustomerName,Employee,DocDate with rollup
  </code></pre>
  <p>But I am getting subtotal as null where it should say <code>Total Jhon Titor</code> as I set it static in the query, also it is repeated for every not aggregated column (3),</p>
  <p><a href="https://i.stack.imgur.com/ENfjq.png" rel="noreferrer"><img src="https://i.stack.imgur.com/ENfjq.png" alt="enter image description here" /></a></p>
  <p>How can I add subtotal and total to the table presented above?</p>
  <p>I am open to use a query without ROLLUP operator. I think it is possible using unions but don't know how to start.</p>
  <p>Thanks for considering my question.</p>