title: Nested group by in SQL Server
body: <p>I have this query as you can see :</p>
  
  <pre><code>SELECT  
      [LineId]
      ,[TestPackageId]
      ,[MaterialDescriptionId]
      ,isnull(sum([RequestQuantity]),0) as TotalMRC
      ,isnull(sum([QuantityDeliver]),0) TotalMIV
      ,isnull(sum([QuantityDeliverToMember]),0) totalIMIV
      ,isnull(sum([QuantityDeliver]),0) - isnull(sum([QuantityDeliverToMember]),0) as Warehouse
  FROM 
      [SPMS2].[dbo].[ViewMTO2] 
  GROUP BY
      [LineId], [TestPackageId], [MaterialDescriptionId]
  </code></pre>
  
  <p>The result is :</p>
  
  <pre><code>lindid   tp     matDes  mrc miv imiv    warehouse
  101973  7927    61075   2   2   0       2
  101991  8666    70721   1   1   0       1
  102052  8751    71008   48  16  0       16
  99626   8053    61075   0   0   0       0
  </code></pre>
  
  <p>The problem is warehouse and the value of that is false .The warehouse value just should be calculated by <code>MaterialDescriptionId</code> The value of warehouse just should be calculated like this :<code>TotalMIV-totalIMIV</code> not for each row (  [LineId],[TestPackageId],[MaterialDescriptionId]) just for each <code>MaterialDescriptionId</code> the true result is </p>
  
  <pre><code>lindid   tp     matDes  mrc miv imiv    warehouse
  101973  7927    61075   2   2   0       2
  101991  8666    70721   1   1   0       1
  102052  8751    71008   48  16  0       16
  99626   8053    61075   0   0   0       2
  </code></pre>
  
  <p>As you can see for both items <code>61075</code> should be 2.because it is calculated based on <code>MaterialDescriptionId</code>.</p>
  
  <p>How can I do that in my query?</p>
  
  <p>here is my <code>viewmto2</code> result  (null values are zero)</p>
  
  <p><a href="https://i.stack.imgur.com/Yiex8.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Yiex8.png" alt="enter image description here"></a></p>