Yes, it should work.

SUM(CASE ..) as

You need to add GROUP BY statement for the columns not involved in the aggregate functions.

>I have the following sproc:
>

>
>IF object_Id('tempdb..#TmpInfo') IS NOT NULL
>	DROP TABLE #TmpInfo
>
>SELECT	trn.iTransaction_ID,
>		trn.iTrade_ID,
>		trn.decTrans_Qty * t.decTrade_Unit_Price AS iShipCost,
>		(trn.decTrans_Qty / t.decTrade_Qty) * t.decTrade_Other_Charge AS iShipPart,
>		(trn.decTrans_Qty / t.decTrade_Qty) * t.decTrade_Other_Charge AS iOtherPart
>	INTO #TmpInfo
>	FROM Transactions trn
>	JOIN Trade t ON t.iTrade_ID = trn.iTrade_ID
>
>
>
>SELECT	
>	/* Misc columns */
>	pc.sProfit_Center_Desc,
>	t.sTrade_Type_Pay_Rec,
>
>	/* Pay-Rec indicator string */
>	CASE t.sTrade_Type_Pay_Rec
>		WHEN 'PAY' THEN 'Sales'
>		WHEN 'REC' THEN 'Purchases'
>		ELSE ''
>	END AS sBS,
>
>	/* Commodity Code = 'G' */
>	CASE p.sCommodity_CD
>		WHEN 'G' THEN ti.iShipCost
>		ELSE 0
>	END AS iG1,
>	CASE p.sCommodity_CD
>		WHEN 'G' THEN ti.iShipPart
>		ELSE 0
>	END AS iG2,
>	CASE p.sCommodity_CD
>		WHEN 'G' THEN ti.iOtherPart
>		ELSE 0
>	END AS iG3,
>
>	/* Commodity Code = 'S' */
>	CASE p.sCommodity_CD
>		WHEN 'S' THEN ti.iShipCost
>		ELSE 0
>	END AS iS1,
>	CASE p.sCommodity_CD
>		WHEN 'S' THEN ti.iShipPart
>		ELSE 0
>	END AS iS2,
>	CASE p.sCommodity_CD
>		WHEN 'S' THEN ti.iOtherPart
>		ELSE 0
>	END AS iS3,
>
>	/* Commodity Code = 'P' */
>	CASE p.sCommodity_CD
>		WHEN 'P' THEN ti.iShipCost
>		ELSE 0
>	END AS iP1,
>	CASE p.sCommodity_CD
>		WHEN 'P' THEN ti.iShipPart
>		ELSE 0
>	END AS iP2,
>	CASE p.sCommodity_CD
>		WHEN 'P' THEN ti.iOtherPart
>		ELSE 0
>	END AS iP3,
>
>	/* Commodity Code = 'L' */
>	CASE p.sCommodity_CD
>		WHEN 'L' THEN ti.iShipCost
>		ELSE 0
>	END AS iL1,
>	CASE p.sCommodity_CD
>		WHEN 'L' THEN ti.iShipPart
>		ELSE 0
>	END AS iL2,
>	CASE p.sCommodity_CD
>		WHEN 'L' THEN ti.iOtherPart
>		ELSE 0
>	END AS iL3,
>
>	/* Other fields */
>	CASE
>		WHEN p.sCommodity_CD IN ('S','P','S','L') THEN 0
>		ELSE ti.iShipCost
>	END	AS iOth1,
>	CASE
>		WHEN p.sCommodity_CD IN ('S','P','S','L') THEN 0
>		ELSE ti.iShipPart
>	END	AS iOth2,
>	CASE
>		WHEN p.sCommodity_CD IN ('S','P','S','L') THEN 0
>		ELSE ti.iOtherPart
>	END	AS iOth3		
>
>FROM Transactions trn
>JOIN #TmpInfo ti ON ti.iTransaction_ID = trn.iTransaction_ID
>JOIN Trade t ON t.iTrade_ID = trn.iTrade_ID
>JOIN Product p ON p.sProduct_CD = trn.sProduct_CD
>LEFT JOIN Profit_Center pc ON pc.sProfit_Center_CD = trn.sProfit_Center_CD
>ORDER BY pc.sProfit_Center_Desc, t.sTrade_Type_Pay_Rec
>

>
>In the second query, I need to sum all the numeric columns. Can I wrap each CASE in a SUM function? How do I go about this?