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.