0

I am trying to define multiple columns in a SELECT statement,
the problem is I keep getting the column "col_name" does not exist error when I try to define a second column name with as for a calculation. this is my query:

SELECT fa.farmer_id as farmer,
  ST_area(fi.geom)/10000 * cr.yieldperhectar as totalyield,
  totalyield * 1000 * cr.priceperkg as revenue,
  revenue - cr.waterdemandpermonth*1980 as income,
  extract(year from wu.usedate) as yyyy
FROM farmers as fa JOIN
  fields as fi ON fa.farmer_id = fi.farmer JOIN
  fieldcrops as fc ON fi.id = fc.field_id JOIN
  crops as cr ON fc.crop_id = cr.crop_id JOIN 
  wateruse as wu ON fi.id = wu.field_id,
  valves as va

and the warning I get is this enter image description here enter image description here
you can ignore the line #6 part, there are commented out lines in my query.

am I doing something wrong here?

EDIT

found the answer here: PostgreSQL Views: Referencing one calculated field in another calculated field, thanks to all the duplicate flags (couldnt find that earlier).
the field has to be recalculated.

Dror Bogin
  • 453
  • 4
  • 13
  • You can't use an alias within the same Select. Eitehr repeat the calculation or use a Derived Table. – dnoeth Jun 14 '18 at 09:12
  • it does work with the first two, meaning i can use `totalyield` and get `revenue` but can't use `revenue` again. – Dror Bogin Jun 14 '18 at 09:14
  • Comment that income calculation and check – dnoeth Jun 14 '18 at 09:17
  • i did, it does. anyway i found the answer [here](https://stackoverflow.com/questions/2385791/postgresql-views-referencing-one-calculated-field-in-another-calculated-field), it has to be recalculated. – Dror Bogin Jun 14 '18 at 09:19

0 Answers0