Mysql – Nested query not recognize external table

aggregateMySQL

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.

Best Answer

The reason for the error is explained in MySQL documentation in part E.4. Restrictions on Subqueries as:

Subqueries in the FROM clause cannot be correlated subqueries. They are materialized in whole (evaluated to produce a result set) during query execution, so they cannot be evaluated per row of the outer query.

Changing query a bit like the following seems to solve your problem:

SELECT t.CodC, c.NameC, t.Amount, t.Year, t.Week, 
    IF(t.Week!=@week OR t.Year!=@year, @rank:=1, @rank:=@rank+1) AS rank, 
    @week:=t.Week, @year:=t.Year 
FROM
    (SELECT CodC, Sum(Imports) As Amount, Year, Week
        FROM Weekly
        WHERE Year=2014
        GROUP BY CodC, Year, Week
        ORDER BY Year DESC, Week DESC, Amount DESC) t
    JOIN (SELECT @rank:=0, @week:=-1, @year:=-1) t2 ON 1
    LEFT JOIN Companies c ON c.CodC=t.CodC

It basically adds an artificial rank column by checking year and week values on the ordered rows.

Edit

Previous query is given to order by weekly sum because of misunderstanding. Following query will give what you want

SELECT t2.*,
    IF(t2.Week!=@week OR t2.Year!=@year, @rank:=1, @rank:=@rank+1) AS rank, 
    @week:=t2.Week, @year:=t2.Year 
FROM
    (SELECT t1.*, SUM(w2.Imports) AS cnt
    FROM
        (SELECT w1.CodC, c.NameC, w1.Week, w1.Year
        FROM Weekly w1
            LEFT JOIN Companies c on c.CodC=w1.CodC
            ORDER BY w1.Year ASC, w1.Week ASC) t1
            LEFT JOIN Weekly w2 ON t1.CodC=w2.CodC AND w2.Year=t1.Year AND w2.Week<=t1.Week
            GROUP BY t1.CodC, t1.Week, t1.Year
            ORDER BY t1.Year DESC, t1.Week DESC, cnt DESC
            )t2
    JOIN (SELECT @rank:=0, @week:=-1, @year:=-1) t3 ON 1

Fiddle

Related Question