Error while using OVER clause and variables

Question

I am trying to use variables to preform a calculation and display it as an aggregate function. The formula calculates the total cost of the product.
@PurchaseCost is how much an item cost per pound. Ex. $5.50
@Prod_CostLBS is how much the labor cost per that pound. Ex. $1.00
@InputWeight is how many pounds there are. Ex. 4,000
All of these variables are input by the user. To calculate the total cost I am using the formula:

CAST ((@PurchaseCost + @Prod_CostLBS) * @InputWeight OVER() AS DECIMAL (18,2))
 AS [Cost]

However, with that I get the error:

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'

I am using Microsoft SQL Server 2005.

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]
   , 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]
   , CAST ((@PurchaseCost + @Prod_CostLBS) * @InputWeight AS DECIMAL (18,2)) AS [Cost]
 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 ([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)) 
   , CAST (@InputWeight - SUM(Sum([IC_ProductLots].[OriginalQuantity_Stk])) OVER () AS DECIMAL(18,2))

I think you don't need to use `OVER()` here. Read [this](http://stackoverflow.com/questions/6218902/over-clause-when-and-why-is-it-useful) ;). — shA.t, Jul 14 '15 at 12:26
@GordonLinoff I am trying to find the total cost which would mean I need the SUM() as you said I believe. If you look at the example numbers it should be (5.50 + 1) * 4,000 — Austin, Jul 14 '15 at 12:29
@shA.t if I remove the over clause I still get the same error. — Austin, Jul 14 '15 at 12:36
Add `CAST ((@PurchaseCost + @Prod_CostLBS) * @InputWeight AS DECIMAL (18,2))` after `GROUP BY` ;). — shA.t, Jul 14 '15 at 12:38
@shA.t then I get the error "Each GROUP BY expression must contain at least one column that is not an outer reference." — Austin, Jul 14 '15 at 12:47
You should be able to have a constant expression in a ``group by`. But, you can put `max()` around it to see if that fixes the problem. — Gordon Linoff, Jul 14 '15 at 12:50
@Austin . . . I am surprised that SQL Server has this limitation, but not surprised that `MAX()` fixes it. There must be something strange going on with the constant expression. Perhaps it has to do with the fact that `cast()` is sometimes non-deterministic (although not in this case). — Gordon Linoff, Jul 14 '15 at 15:43

Madhivanan · Answer 1 · 2015-07-14T12:39:02.213

0

Can you post the full code? Try this

CAST ((@PurchaseCost + @Prod_CostLBS) * @InputWeight AS DECIMAL (18,2))
 AS [Cost]

Also instead of

Instead of CAST (@InputWeight - SUM(Sum([IC_ProductLots].[OriginalQuantity_Stk])) OVER () AS DECIMAL(18,2)) AS [Shrink]

use

 `CAST (@InputWeight - SUM([IC_ProductLots].[OriginalQuantity_Stk])
 AS DECIMAL(18,2)) AS [Shrink]

edited Jul 14 '15 at 12:39

answered Jul 14 '15 at 12:24

Madhivanan

13,470
1
24
29

the Shrink works properly though and if I change it to that it no longer displays it as an aggregate function, it shows it for every column. I am having a problem with the [COST] – Austin Jul 14 '15 at 12:43

Error while using OVER clause and variables

1 Answers1