CODEWITHSUNDEEP
postgresqlfunctionreturn
Tere
Tere

Reputation: 1

Column Reference ambiguous

I keep receiving "ERROR: column reference "delta_hh_2010to2011" is ambiguous" for the following code:

Would anyone happen to know if I am missing something or to disambiguate the column? Thanks! 

DROP TABLE IF EXISTS us2011;
CREATE TABLE us2011 AS (
SELECT stateabbr, block_fips, hu2010, hh2010, pop2010
--    , hu2010+delta_hu_2010to2011 as hu2011, greatest(0,hh2010+delta_hh_2010to2011) as hh2011, pop2010+delta_pop_2010to2011 as pop2011
  , hu2010 + delta_hu_2010to2011  as hu2011, greatest(0, hh2010 + delta_hh_2010to2011) as hh2011, pop2010 + delta_pop_2010to2011 as pop2011 
--    , hu2011+delta_hu_2011to2012  as hu2012,greatest(0,hh2011+delta_hh_2011to2012) as hh2012, pop2011+delta_pop_2011to2012 as pop2012 
--    , hu2011+delta_hu_2010to2011+delta_hu_2011to2012  as hu2012, greatest(0,hh2011+delta_hh_2011to2012) as hh2012, pop2011+delta_pop_2011to2012 as pop2012 
FROM (
SELECT  upper(stateabbr) as stateabbr, block_fips, hu2010, hh2010, pop2010
,sum(coalesce(CASE WHEN startyear=2010 THEN new_road_hu   END,0))+sum(coalesce(CASE WHEN startyear=2010 THEN removed_hu END,0))
+sum(coalesce(CASE WHEN startyear=2010 THEN added_hu END,0)) as delta_hu_2010to2011
 -- , CASE WHEN hu2010>0 THEN hh2010::numeric/hu2010::numeric END, county_hh_ratio 
 -- , CASE WHEN hh2010=0 THEN county_hh_ratio ELSE hh2010::numeric/hu2010::numeric END 
, round(CASE WHEN hh2010=0 THEN county_hh_ratio ELSE hh2010::numeric/hu2010::numeric END * (sum(coalesce(new_road_hu,0))+ sum(coalesce(removed_hu,0))+ sum(coalesce(added_hu,0))),4) as delta_hh_2010to2011
, sum(coalesce(new_road_hu,0))+sum(coalesce(removed_hu,0))+sum(coalesce(added_hu,0))*(CASE WHEN hh2010=0 THEN county_hh_ratio ELSE hh2010/hu2010::numeric END * sum(coalesce(new_road_hu,0))+sum(coalesce(removed_hu,0))+sum(coalesce(added_hu,0))) as delta_hh_2010to2011
, sum(coalesce(new_road_pop,0))+sum(coalesce(removed_pop,0))+sum(coalesce(added_pop,0)) as delta_pop_2010to2011
FROM us2010 LEFT JOIN counts.new_counts USING (block_fips) LEFT JOIN county_hh_per_hu ON left(block_fips,5)=county_fips
GROUP BY block_fips, stateabbr, hu2010, hh2010, pop2010, county_hh_ratio
) foo);

Sorry for the long code! Thanks again!

Upvotes: 0

Views: 656

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51509

you use same alias twice:

, round(CASE WHEN hh2010=0 THEN county_hh_ratio ELSE hh2010::numeric/hu2010::numeric END * (sum(coalesce(new_road_hu,0))+ sum(coalesce(removed_hu,0))+ sum(coalesce(added_hu,0))),4) as delta_hh_2010to2011
, sum(coalesce(new_road_hu,0))+sum(coalesce(removed_hu,0))+sum(coalesce(added_hu,0))*(CASE WHEN hh2010=0 THEN county_hh_ratio ELSE hh2010/hu2010::numeric END * sum(coalesce(new_road_hu,0))+sum(coalesce(removed_hu,0))+sum(coalesce(added_hu,0))) as delta_hh_2010to2011

change to 

, round(CASE WHEN hh2010=0 THEN county_hh_ratio ELSE hh2010::numeric/hu2010::numeric END * (sum(coalesce(new_road_hu,0))+ sum(coalesce(removed_hu,0))+ sum(coalesce(added_hu,0))),4) 
as delta_hh_2010to2011
, sum(coalesce(new_road_hu,0))+sum(coalesce(removed_hu,0))+sum(coalesce(added_hu,0))*(CASE WHEN hh2010=0 THEN county_hh_ratio ELSE hh2010/hu2010::numeric END * sum(coalesce(new_road_hu,0))+sum(coalesce(removed_hu,0))+sum(coalesce(added_hu,0))) 
as delta_hh_2010to2011_other

Upvotes: 3

Related Questions