Question 113100 in Mysql Question posted in
The official MySQL documentation can be found here.

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

Tags:

2

Answers


  1. 0

    You don’t have a table named i1 in your second example, yet you’re querying values from it. I believe what you want is something like

    select DT.Month, DT.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 DT.Month >=i2.Month
group by DT.Month, DT.Rate
order by Month DESC

    Which replaces your i1 references with DT.

    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search