CODEWITHSUNDEEP
sqlsql-server-2005aggregate-functionscumulative-sum
Austin
Austin

Reputation: 105

Aggregate function error for calculation

For a report I am creating I need to take the calculated SUM and use that for another equation. The equation is used to calculate the Shrink, which is the same thing as the difference. To calculate the Shrink I use:

@InputWeight - Sum([IC_ProductLots].[OriginalQuantity_Stk]) AS [Shrink]

The input weight is given by the user before running the report. So for example, if the input weight is 15,000 and the SUM of the original quantity is 14,800 then the Shrink should be 200.

The problem is that that code is calculating the Shrink for each row and each report should only have ONE Shrink.

An example output would look like this:

Input Weight: 15,000
Product # | Weight | Shrink
1234      | 7,000  | 
1235      | 6,500  | 
1236      | 500    | 
1237      | 800    | 
--------------------------
TOTAL     |14,800  | 200

I am using Microsoft SQL Server 2005.

Below is my full code if that helps:

SET NOCOUNT ON; 
DECLARE @PurchaseCost Decimal(19,8);
DECLARE @InputWeight Decimal(19,8);

SET @PurchaseCost = 1;
SET @InputWeight = 15000;
    SELECT DISTINCT 
         CAST([ARC].[CustomerCode] AS NVARCHAR(40)) + ' - ' + CAST([ARC].[Name] AS NVARCHAR(40)) AS [Supplier]
       , [PC].ProductCode
       , [PC].Description1
       , Count(IC_ProductLots.OriginalQuantity_Alt) AS [Boxes]
       , IC_ProductLots.UnitOfMeasure_Alt
       , Sum(IC_ProductLots.OriginalQuantity_Stk) AS [Weight]
       , IC_ProductLots.UnitOfMeasure_Stk
       , [ICP].UnitCost AS [Unit Cost]
       , Sum(ROUND([DCT].[Quantity_Stk] *[ICP].[UnitCost], 2)) AS [Total Sales]
       , Avg(([IC_ProductLots].[OriginalQuantity_Stk] / [IC_ProductLots].[OriginalQuantity_Alt])) AS [Avg. Box Weight]
       , Sum([IC_ProductLots].[OriginalQuantity_Stk] / @InputWeight) AS [Yield]
       , @InputWeight - Sum([IC_ProductLots].[OriginalQuantity_Stk]) AS [Shrink]
     FROM (((( IC_Products [PC] 
        INNER JOIN  DC_Transactions [DCT] 
         ON [PC].ProductKey = [DCT].ProductKey)
        INNER JOIN  AR_Customers [ARC] 
         ON [DCT].CustomerKey = [ARC].CustomerKey)
        INNER JOIN  IC_ProductLots 
         ON [DCT].LotKey = IC_ProductLots.LotKey)
        LEFT OUTER JOIN  IC_ProductCosts [ICP] 
         ON ICP.ProductKey=PC.ProductKey and ICP.ProductCostCode=5)
     WHERE 
        (IC_ProductLots.ProductionDate >= { ts '2015-06-29 00:00:00' }   AND (IC_ProductLots.ProductionDate <= { ts '2015-06-29 00:00:00' } OR IC_ProductLots.ProductionDate Is Null)) 
    AND ([ARC].CustomerCode = '         904') 
     GROUP BY 
         CAST([ARC].[CustomerCode] AS NVARCHAR(40)) + ' - ' + CAST([ARC].[Name] AS NVARCHAR(40))
       , [PC].ProductCode
       , [PC].Description1
       , IC_ProductLots.UnitOfMeasure_Alt
       , IC_ProductLots.UnitOfMeasure_Stk
       , [ICP].UnitCost
       , IC_ProductLots.ProductionDate
       , [ARC].CustomerCode
     ORDER BY 
         CAST([ARC].[CustomerCode] AS NVARCHAR(40)) + ' - ' + CAST([ARC].[Name] AS NVARCHAR(40))

Basically, I need to calculate the total shrink and not a shrink for each individual row.

Updates:

I tried to use 

@InputWeight - Sum([IC_ProductLots].[OriginalQuantity_Stk]) OVER(PARTITION BY [ARC].[CustomerCode]) AS [Shrink]

But I get the error: "Column 'IC_ProductLots.OriginalQuantity_Stk' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

Upvotes: 0

Views: 49

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

I think the expression you want is:

@InputWeight - SUM(Sum([IC_ProductLots].[OriginalQuantity_Stk])) OVER ()

Note: you don't need to use select distinct with group by, so you should remove the distinct.

Upvotes: 1

Related Questions