Nested group by in SQL Server
<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>