i need to a query that show week by week the total amount of imports of any company and in addition add a ranking position depending on that amount.
I've made this query but the problem is the inner select does not recognize the w1 table.
MySql give me this error:
#1054 - Unknown column 'w1.Year' in 'where clause'
SELECT w1.CodC, c.NameC, w1.Week, w1.Year, ( SELECT @rn:=@rn+1 FROM( SELECT w2.CodC as Code, Sum(w2.Imports) As Amount FROM Weekly w2 WHERE w2.Year=w1.Year AND w2.Week<=w1.Week GROUP BY w2.CodC order by Amount DESC ) tab1, ( SELECT @rn:=0 ) tab2 ) AS RkAmount FROM Weekly w1 LEFT JOIN Companies c on c.CodC=w1.CodC ORDER BY w1.Year DESC, w1.Week DESC, RkAmount ASC
How can i proceed?
Thanks a lot
The structure of Weekly table is:
CodC - Week - Year - Imports
and contains one entry for each society for each week if the import is >0.
Table Companies is only a Company description (in this query usefull only for name decoding).
And finally what i want is to Rank Companies by Imports sum from the beginning of the year.
Here an example:
CodC - W - Year - Import AAAA - 1 - 2014 - 1000 BBBB - 1 - 2014 - 500 AAAA - 2 - 2014 - 700 BBBB - 2 - 2014 - 200 CCCC - 2 - 2014 - 2000
What i wanna output is
CodC - NameC - W - Year - RkAmount CCCC - CompC - 2 - 2014 - 1 AAAA - CompA - 2 - 2014 - 2 BBBB - CompB - 2 - 2014 - 3 AAAA - CompA - 1 - 2014 - 1 BBBB - CompB - 1 - 2014 - 2
I hope this example has been more explanatory.