InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 19:31:14 GMT20http://www.sqlservercentral.com/Forums/Topic1426427-1292-1.aspxAnd thanks Lynn Pettis! Just saw your post.Mon, 04 Mar 2013 13:54:14 GMThogpenhttp://www.sqlservercentral.com/Forums/Topic1426427-1292-1.aspxThanks Evil Kraig F! Exactly what I needed.Mon, 04 Mar 2013 13:53:21 GMThogpenhttp://www.sqlservercentral.com/Forums/Topic1426427-1292-1.aspxBased on your code, something like this will work:[code="sql"]WITH BaseData as (SELECT a.PLANT_NO AS PlantNumber, SUM(a.APPLIED_VOL) AS AppliedVolumeFROM [EdwStaging].[RAW].[FactSettleFee_TIPS_QRMTIPS_QPOST_SETTLE_FEE] aGROUP BY a.PLANT_NOUNION ALLSELECT CASE WHEN a.plant_no = 'ALL' THEN e.[PlantNumber] ELSE a.plant_no END AS ResolvedPlantNumber, SUM(TRANS_VOL) AS AppliedVolumeFROM [EdwStaging].[RAW].[FactSettleFee_TIPS_QRMTIPS_QPOST_RPTS_INVOICE_DTL] a LEFT OUTER JOIN [EdwStaging].[PSTG].[ContractToPlantMapping] e ON a.ctr_no = e.[ContractNumber] AND a.PROD_DT BETWEEN e.[PlantEffectiveFromDate] AND e.[PlantEffectiveToDate] AND a.PROD_DT BETWEEN e.[ContractEffectiveFromDate] AND e.[ContractEffectiveToDate] AND a.plant_no = 'ALL'GROUP BY CASE WHEN a.plant_no = 'ALL' THEN e.[PlantNumber] ELSE a.plant_no END )select PlantNumber, sum(AppliedVolume)from BaseDatagroup by PlantNumber;[/code]Mon, 04 Mar 2013 13:36:38 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1426427-1292-1.aspxYou're good with what you're using UNION for, but you have to keep in mind that UNION vs. UNION ALL is the equivalent of using a DISTINCT or not on the query. It's not grouping/aggregating.So, for starters, you want to use UNION ALL there because you might otherwise be losing data that you need to feed into your aggregation, unless you can straight dupe rows between the two queries.Next, what you'll want to do is aggregate the union. It'll basically look like this:[code="sql"]SELECT SiteCode, SUM( SomeStuff) AS SumOfStuffFROM (SELECT SiteCode, SomeStuff FROM table1 UNION ALL SELECT SiteCode, SomeStuff From Table2 ) AS drvGROUP By SiteCode[/code]Mon, 04 Mar 2013 13:13:31 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1426427-1292-1.aspxI'm guessing I'm misunderstanding how to use UNION or its results. But here's what I have and the results:SELECT a.PLANT_NO AS PlantNumber ,SUM(a.APPLIED_VOL) AS AppliedVolume FROM [EdwStaging].[RAW].[FactSettleFee_TIPS_QRMTIPS_QPOST_SETTLE_FEE] aGROUP BY a.PLANT_NOUNIONSELECT CASE WHEN a.plant_no = 'ALL' THEN e.[PlantNumber] ELSE a.plant_no END AS ResolvedPlantNumber ,SUM(TRANS_VOL) AS AppliedVolume FROM [EdwStaging].[RAW].[FactSettleFee_TIPS_QRMTIPS_QPOST_RPTS_INVOICE_DTL] aLEFT OUTER JOIN [EdwStaging].[PSTG].[ContractToPlantMapping] e ON a.ctr_no = e.[ContractNumber] AND a.PROD_DT BETWEEN e.[PlantEffectiveFromDate] AND e.[PlantEffectiveToDate] AND a.PROD_DT BETWEEN e.[ContractEffectiveFromDate] AND e.[ContractEffectiveToDate] AND a.plant_no = 'ALL'GROUP BY CASE WHEN a.plant_no = 'ALL' THEN e.[PlantNumber] ELSE a.plant_no END Example result:PlantNumber --- AppliedVolume002 --- 324334.00 002 --- 215943448.81043 --- 3513440.00043 --- 407522014.65Is there a way for me to have the plant numbers grouped together so there aren't duplicates?Such as:PlantNumber --- AppliedVolume002 ---- 216,267,782.81 043 --- 411,035,454.65 Mon, 04 Mar 2013 13:09:24 GMThogpen