Code:
USE Database
Select <a bunch of things>,
sum(Total),
(sum(TotalVal)/sum(Total)) as Avprice,
sum (TotalVal)
FROM (Select (CASE WHEN OM.Currency = 'EUR' THEN ((OP.Charge_Price_Per_Unit*0.88)/Sum(OP.Qty)) ELSE (OP.Qty*OP.Charge_Price_Per_Unit)/Sum(OP.Qty) END) as avprice,
SUM(CASE WHEN OM.Currency = 'EUR' THEN (OP.Qty*(OP.Charge_Price_Per_Unit*0.88)) ELSE (OP.Qty*OP.Charge_Price_Per_Unit) END) AS totalval,
SUM(OP.Qty) AS total, OP.Product_Id, Site
FROM order_main AS OM with (NOLOCK)
JOIN order_product AS OP with (NOLOCK)
on OP.order_number = OM.order_number
where OM.order_created > GETDATE()-1
and OM.order_created < GETDATE()
group by OP.product_id, site
) salesThis won't run because the columns in the CASE WHEN aren't aggregated, but I don't want to put them in the group by - when I do it screws up some other stuff.
Any advice please?
Cheers