Trying to calculate aggregate functions

Transact-SQL
1

I've been having several problems using OVER and CAST while attempting to do aggregate calculations for my report.

I was able to get help for two of my previous calculations and now I am having trouble with the third. This time I am trying to calculate the profit. To do this I need to use the "Total Sales" which is [DCT].[Quantity_Stk] *[ICP].[UnitCost] and subtract that by the "Cost" which is (@PurchaseCost + @Prod_CostLBS) * @InputWeight

Currently I am using the expression:

CAST (([DCT].[Quantity_Stk] *[ICP].[UnitCost]) - ((@PurchaseCost + @Prod_CostLBS) * @InputWeight) OVER () AS DECIMAL(18,2)) AS [PROFIT]

With this I get the error message: The same Query View cannot show a column with a Group designation of 'Expression' without an aggregate function when another column contains a Group designation of 'Group By' Previously, I was able to display the report without an error but the value was completely off.

I also tried:

CAST (SUM(Sum([DCT].[Quantity_Stk] *[ICP].[UnitCost])) - ((@PurchaseCost + @Prod_CostLBS) * @InputWeight) OVER () AS DECIMAL(18,2))

I need it to an aggregate function.

Here is my full code:

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

SET @PurchaseCost = 2.58;
SET @InputWeight = 18100;
SET @Prod_CostLBS  = .15;

SELECT 
     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]
   , [ARC].CustomerKey
   , Sum([DCT].[Quantity_Stk] *[ICP].[UnitCost]) AS [Total Sales]
   , Avg(([IC_ProductLots].[OriginalQuantity_Stk] / [IC_ProductLots].[OriginalQuantity_Alt])) AS [Avg. Box Weight]
   , Sum([IC_ProductLots].[OriginalQuantity_Stk] / @InputWeight) AS [Yield]
   , CAST (@InputWeight - SUM(Sum([IC_ProductLots].[OriginalQuantity_Stk])) OVER () AS DECIMAL(18,2)) AS [Shrink]
   , Max(CAST ((@PurchaseCost + @Prod_CostLBS) * @InputWeight AS DECIMAL (18,2))) AS [COST]
   , Max(CAST (([DCT].[Quantity_Stk] *[ICP].[UnitCost]) - ((@PurchaseCost + @Prod_CostLBS) * @InputWeight) OVER () AS DECIMAL(18,2))
) AS [Profit]
 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-24 00:00:00' }   AND (IC_ProductLots.ProductionDate <= { ts '2015-06-24 00:00:00' } OR IC_ProductLots.ProductionDate Is Null)) 
AND ((1=1)  AND [ARC].CustomerKey IN (124) ) 
 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].CustomerKey
 ORDER BY 
     CAST([ARC].[CustomerCode] AS NVARCHAR(40)) + ' - ' + CAST([ARC].[Name] AS NVARCHAR(40)) 
   , CAST (@InputWeight - SUM(Sum([IC_ProductLots].[OriginalQuantity_Stk])) OVER () AS DECIMAL(18,2)) 
   , Max(CAST ((@PurchaseCost + @Prod_CostLBS) * @InputWeight AS DECIMAL (18,2)))

I am using Microsoft SQL Server 2005.

2

I didn't understand all the logic you are trying to implement, but there are two rules that you need to follow.

  1. Every column in the SELECT list must also be in the GROUP by list unless those columns are inside an aggregate function. Aggregate functions used with OVER clause do not count, those are an exception. Such columns should be listed in the group by clause as well.

  2. If you want to use OVER clause, it has to be preceded by an aggregate function.

The following example might make it clearer. I have 3 columns col1, col2, col3. I am listing col2 and col3 in the GROUP by clause because they are in the SELECT list, but are not in an aggregate function. col2 is inside a SUM function, but that is a SUM-OVER, which does not count.

CREATE TABLE #tmp(col1 INT, col2 INT, col3 INT);
INSERT INTO #tmp VALUES
(1,1,1),(2,2,2),(3,3,3),(4,4,4);
GO


SELECT
	SUM(col1),
	SUM(col2) OVER(),
	col3
FROM
	#tmp
GROUP BY
	col2, col3;
	
GO

DROP TABLE #tmp;
3

As I noticed you have used over() without any parameter (Over should have partition by and Order by )
and ProductionDate is used in group by but ProductionDate is not used in from clause.