CODEWITHSUNDEEP
sqlsql-servert-sql
John
John

Reputation: 1499

Anyone know why this UNION is giving me a certain Error, I see nothing wrong

I've shown the Error first and then the SQL with the bit i'm adding in commented out, in this state it works fine. If I comment back in the UNION part i get the error. IT all works and PremiseMeteredPricings is identical to PremisePricings field wise etc and can easily do a normal UNION.

THE ERROR: Msg 207, Level 16, State 1, Line 31 Invalid column name 'PremiseId'. Msg 207, Level 16, State 1, Line 18 Invalid column name 'PremiseId'.

THE SQL 

select PremiseId,
  sum(case when PricingCategory = 'Water' and WholesalePricing=0 then FixedCharge end) RetailWaterFixed,sum(case when PricingCategory = 'Water' and WholesalePricing=0 then VolumetricCharge end) RetailWaterVar,
  sum(case when PricingCategory = 'Waste' and WholesalePricing=0 then FixedCharge end) RetailWasteFixed,sum(case when PricingCategory = 'Waste' and WholesalePricing=0 then VolumetricCharge end) RetailWasteVar,
  sum(case when PricingCategory = 'Roads Drainage' and WholesalePricing=0 then FixedCharge end) RetailRoadsFixed,sum(case when PricingCategory = 'Roads Drainage' and WholesalePricing=0 then VolumetricCharge end) RetailRoadsVar,
  sum(case when PricingCategory = 'Property Drainage' and WholesalePricing=0 then FixedCharge end) RetailPropertyFixed,sum(case when PricingCategory = 'Property Drainage' and WholesalePricing=0 then VolumetricCharge end) RetailPropertyVar,

  sum(case when PricingCategory = 'Water' and WholesalePricing=1 then FixedCharge end) WholesaleWaterFixed,sum(case when PricingCategory = 'Water' and WholesalePricing=1 then VolumetricCharge end) WholesaleWaterVar,
  sum(case when PricingCategory = 'Waste' and WholesalePricing=1 then FixedCharge end) WholesaleWasteFixed,sum(case when PricingCategory = 'Waste' and WholesalePricing=1 then VolumetricCharge end) WholesaleWasteVar,
  sum(case when PricingCategory = 'Roads Drainage' and WholesalePricing=1 then FixedCharge end) WholesaleRoadsFixed,sum(case when PricingCategory = 'Roads Drainage' and WholesalePricing=1 then VolumetricCharge end) WholesaleRoadsVar,
  sum(case when PricingCategory = 'Property Drainage' and WholesalePricing=1 then FixedCharge end) WholesalePropertyFixed,sum(case when PricingCategory = 'Property Drainage' and WholesalePricing=1 then VolumetricCharge end) WholesalePropertyVar

from PremisePricings 
where UserId = 'cdb370f7-b995-4d99-adc9-c00c7e837bb4'
group by PremiseId

UNION 

 select PremiseId,
  sum(case when PricingCategory = 'Water' and WholesalePricing=0 then FixedCharge end) RetailWaterFixed,sum(case when PricingCategory = 'Water' and WholesalePricing=0 then VolumetricCharge end) RetailWaterVar,
  sum(case when PricingCategory = 'Waste' and WholesalePricing=0 then FixedCharge end) RetailWasteFixed,sum(case when PricingCategory = 'Waste' and WholesalePricing=0 then VolumetricCharge end) RetailWasteVar,
  sum(case when PricingCategory = 'Roads Drainage' and WholesalePricing=0 then FixedCharge end) RetailRoadsFixed,sum(case when PricingCategory = 'Roads Drainage' and WholesalePricing=0 then VolumetricCharge end) RetailRoadsVar,
  sum(case when PricingCategory = 'Property Drainage' and WholesalePricing=0 then FixedCharge end) RetailPropertyFixed,sum(case when PricingCategory = 'Property Drainage' and WholesalePricing=0 then VolumetricCharge end) RetailPropertyVar,

  sum(case when PricingCategory = 'Water' and WholesalePricing=1 then FixedCharge end) WholesaleWaterFixed,sum(case when PricingCategory = 'Water' and WholesalePricing=1 then VolumetricCharge end) WholesaleWaterVar,
  sum(case when PricingCategory = 'Waste' and WholesalePricing=1 then FixedCharge end) WholesaleWasteFixed,sum(case when PricingCategory = 'Waste' and WholesalePricing=1 then VolumetricCharge end) WholesaleWasteVar,
  sum(case when PricingCategory = 'Roads Drainage' and WholesalePricing=1 then FixedCharge end) WholesaleRoadsFixed,sum(case when PricingCategory = 'Roads Drainage' and WholesalePricing=1 then VolumetricCharge end) WholesaleRoadsVar,
  sum(case when PricingCategory = 'Property Drainage' and WholesalePricing=1 then FixedCharge end) WholesalePropertyFixed,sum(case when PricingCategory = 'Property Drainage' and WholesalePricing=1 then VolumetricCharge end) WholesalePropertyVar

 from PremiseMeteredPricings
 where UserId = 'cdb370f7-b995-4d99-adc9-c00c7e837bb4'
group by PremiseId

Upvotes: 0

Views: 28

Answers (1)

sgeddes
sgeddes

Reputation: 62841

You need a second group by statement with your union:

SELECT ...
FROM premises p
INNER JOIN
(
select PremiseId,
  sum(case when PricingCategory = 'Water' and WholesalePricing=0 then FixedCharge end) RetailWaterFixed,sum(case when PricingCategory = 'Water' and WholesalePricing=0 then VolumetricCharge end) RetailWaterVar,
  ...
from PremisePricings 
where UserId = 'cdb370f7-b995-4d99-adc9-c00c7e837bb4'    <--Here
group by PremiseId                                       <--Here 
union
select PremiseId,
  sum(case when PricingCategory = 'Water' and WholesalePricing=0 then FixedCharge end) RetailWaterFixed,sum(case when PricingCategory = 'Water' and WholesalePricing=0 then VolumetricCharge end) RetailWaterVar,
  ...
from PremiseMeteredPricings
where UserId = 'cdb370f7-b995-4d99-adc9-c00c7e837bb4'    <--And again here
group by PremiseId                                       <--And again here
) x on x.PremiseId = p.PremiseId 
order by CoreSPID

Upvotes: 1

Related Questions