0

I want to rename a column after calculating the difference between current row and previous row by using over clause (windows function)

However, I cannot run it and it shows that I have a error in my sql syntax

select country,year,
avg(oil_consumption) OVER (partition by country 
  order BY year 
     ROWS BETWEEN 2 PRECEDING AND current row )
     as 3_year_moving_average, (3_year_moving_average-lag(3_year_moving_average,1) over (partition by country) **as** difference_between_previous_current
from owid_energy_data

Error at the second as (bolded)

Felix D.
  • 4,811
  • 8
  • 38
  • 72
Shannon
  • 1
  • parentheses are unbalanced – Tim Biegeleisen Nov 08 '22 at 09:03
  • And you cannot compute an expression as a column value and then reference that result using the column name in the same `SELECT` clause - all column values are computed *as if* they're being computed in parallel, they cannot be interdependent. – Damien_The_Unbeliever Nov 08 '22 at 09:08
  • May I know how can I reuse that calculation then? I have tried using user-defined varaible but it is session specific @Damien_The_Unbeliever – Shannon Nov 09 '22 at 06:56
  • Thank you! I have solved this issue :) @TimBiegeleisen – Shannon Nov 09 '22 at 06:58
  • @Shannon - depending on which RDBMS you're using (if you want specifics, please add an appropriate tag as the [tag:sql] tag suggests you do), there'll be options to apply lateral joins, CTEs or subqueries such that you place the earlier calculation into a *separate* `SELECT` clause that a later `SELECT` clause can then reference – Damien_The_Unbeliever Nov 09 '22 at 07:00

0 Answers0