CODEWITHSUNDEEP
sql-servergroup-byaggregate-functions
Ehsan Akbar
Ehsan Akbar

Reputation: 7301

Nested group by in SQL Server

I have this query as you can see :

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]

The result is :

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

The problem is warehouse and the value of that is false .The warehouse value just should be calculated by MaterialDescriptionId The value of warehouse just should be calculated like this :TotalMIV-totalIMIV not for each row ( [LineId],[TestPackageId],[MaterialDescriptionId]) just for each MaterialDescriptionId the true result is 

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

As you can see for both items 61075 should be 2.because it is calculated based on MaterialDescriptionId.

How can I do that in my query?

here is my viewmto2 result (null values are zero)

enter image description here

Upvotes: 1

Views: 4889

Answers (2)

Rominus
Rominus

Reputation: 1221

Looks like you need a subquery calculating the grouped warehouse value by MaterialDescriptionId:

SELECT  
    a.[LineId]
    ,a.[TestPackageId]
    ,a.[MaterialDescriptionId]
    ,isnull(sum(a.[RequestQuantity]),0) as TotalMRC
    ,isnull(sum(a.[QuantityDeliver]),0) TotalMIV
    ,isnull(sum([a.QuantityDeliverToMember]),0) totalIMIV
    ,b.Warehouse
FROM 
    [SPMS2].[dbo].[ViewMTO2] as a
INNER JOIN
    (SELECT
    MaterialDescriptionId, isnull((QuantityDeliver) - sum(QuantityDeliverToMember),0) as Warehouse
    FROM [SPMS2].[dbo].[ViewMTO2]
    GROUP BY MaterialDescriptionId) as b
ON 
a.MaterialDescriptionId = b.MaterialDescriptionId   
GROUP BY
    a.[LineId], a.[TestPackageId], a.[MaterialDescriptionId], b.Warehouse

Upvotes: 0

Marc Guillot
Marc Guillot

Reputation: 6465

You can use separate subqueries to calculate Warehouse :

SELECT  
    [LineId]
    ,[TestPackageId]
    ,[MaterialDescriptionId]
    ,isnull(sum([RequestQuantity]),0) as TotalMRC
    ,isnull(sum([QuantityDeliver]),0) TotalMIV
    ,isnull(sum([QuantityDeliverToMember]),0) totalIMIV
    ,(select isnull(sum(b.[QuantityDeliver]),0) from viewmto2 b where b.[MaterialDescriptionId] = a.[MaterialDescriptionId]) -
     (select isnull(sum(b.[QuantityDeliverToMember]),0) from viewmto2 b where b.[MaterialDescriptionId] = a.[MaterialDescriptionId]) as Warehouse
FROM 
    [SPMS2].[dbo].[ViewMTO2] a
GROUP BY
    [LineId], [TestPackageId], [MaterialDescriptionId]

Upvotes: 1

Related Questions