mysqlderived-table

MySQL - Derived table query not fetching data for nested Select query


I have a quey which has growth rates over a period of time. I am trying to obtain the overall growth between two rows that I specify. Here is the SQL fiddle http://sqlfiddle.com/#!9/1756ca/2

select i1.Month, i1.Rate,  EXP(SUM(LOG(1+i2.Rate)))   InfRate 
from Inflation i1
inner join Inflation i2 on i1.Month >=i2.Month
group by i1.Month, i1.Rate
order by Month DESC 

This seems to work correctly and I am able to get the growth rate for the entire Month range in the fiddle, however I am trying to use a derived table so that I can specify the Month period, like this, however it is not working

select i1.Month, i1.Rate,  EXP(SUM(LOG(1+i2.Rate)))   InfRate 
from (SELECT * FROM `Inflation` WHERE Month between '2020-01-01' and '2022-01-01') as DT
inner join Inflation i2 on i1.Month >=i2.Month
group by i1.Month, i1.Rate
order by Month DESC 

I get the error #1054 - Unknown column 'i1.Month' in 'field list'

I am trying to use a derived table for the period between '2020-01-01' and '2022-01-01' or any other range that I specify, however it does not seem to be working for me. Any help will be appreciated.

The expected result is something like this, considering that only the period between '2020-01-01' and '2022-01-01' was queried

http://sqlfiddle.com/#!9/13f818/1

There seems to be some problem with the fiddle, here is an updated one

https://dbfiddle.uk/TwQ7VWs2

enter image description here


Solution

  • If you are looking for "inflation since the start of 2020, you need to limit i2:

    Select DT.Month, DT.Rate,  EXP(SUM(LOG(1+i2.Rate)))   InfRate 
    from (SELECT * FROM `Inflation` WHERE Month between '2020-01-01' and '2022-06-01') as DT
    inner join Inflation i2 on DT.Month >=i2.Month
    where i2.Month >= '2020-01-01'   -- I added this
    group by DT.Month, DT.Rate
    order by Month DESC;