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
Edit
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.
1,2,3
rankings in theRkAmount
come from the2000,1700,700
(cumulative sums), right?