0

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.

4
  • The expected output is not clear. Do you want all weeks for a year for every company, even if for some week there are no data for a company? Is the ranking for companies (per week)? Or for weeks (per company)? Or one ranking for all? Feb 12, 2014 at 15:24
  • Thanks for answer. I've edit my post with more detail on tables and a full example of what i wanna obtain. Feb 13, 2014 at 10:09
  • I guess the 1,2,3 rankings in the RkAmount come from the 2000,1700,700 (cumulative sums), right? Feb 13, 2014 at 10:12
  • Cit: And finally what i want is to Rank Companies by Imports sum from the beginning of the year. ... So yes, you're right :) Feb 13, 2014 at 11:36

1 Answer 1

1

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

2
  • The result is different. The Amount column is not the sum for that company from the beginning of the year, but only the sum for that company for the specific week (that is equal to not sum because there is only an entry). If i don't group by week i get the correct solution but only for the last week, and i don't have the older. Feb 13, 2014 at 13:25
  • Edited my answer.
    – chingeez
    Feb 21, 2014 at 10:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.