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

Question

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

@Booboo Yes, even I am not able to access it now, nor am I able to create a new one on SQLFiddle. Here is another https://dbfiddle.uk/TwQ7VWs2 — Quark, Oct 22 '22 at 14:29
Thanks @RickJames. Can you please see this dbfiddle.uk/TwQ7VWs2 — Quark, Oct 22 '22 at 14:31
Do you want the inflation since the start of 2020? And compute that for every month since then? (Please phrase the goal is words like this.) — Rick James, Oct 22 '22 at 20:05
@RickJames, the goal is to obtain the inflation for the period I specify in WHERE Month between '2020-01-01' and '2022-01-01', so in this case, the inflation in 202-01-01 would begin at 1 (plus whatever the inflation is for that period, 0.00310833 in this case, so 1.00310833) and the end month would be 2022-01-01, my Excel calculation shows the inflation for this period as 1.113519. So it would be 1*(0.00310833+1)=1.003108, the next would be 1.003108*(0.00310833+1)=1.006226, 1.006226*(0.00310833+1)=1.009354, 1.01249, 1.01563 and so on... — Quark, Oct 22 '22 at 21:48
@RickJames, added a screenshot from my Excel calculation to the original question, can you please review it — Quark, Oct 22 '22 at 21:49

score 1 · Answer 1 · answered Oct 19 '22 at 19:03

1

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.

answered Oct 19 '22 at 19:03

DrGodCarl

1,666
1
12
17

Hello @DrGodCarl, I noticted that the results remain the same even after I change Month between '2020-01-01' and '2022-01-01' to something else. Essentially, I want the starting month '2020-01-01' to be 1 and then the subsequent growth/inflation should use this as the reference. The InfRate for 2020-01-01 is 1.0015 and then when I change the reference month to 2020-01-01, it builds upon the original value and uses the value of 1.0192, I want this to be 1. Can you please advise on this? – Quark Oct 19 '22 at 20:17

score 1 · Accepted Answer · answered Oct 22 '22 at 20:04

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;

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

2 Answers2