Mysql Ranking based on Category and Branch

Question

I'm having a hard time figuring out and trying how to fix this. Can you help me give a logic or idea how can get the ranking of each category for each branch based on sales?

For example:

• Rank 1 for branch_code_id = 9 is Accicular since it has 300,000 sales
• Rank 2 for branch_code_id = 9 is WLO since it has only 200,000 sales.

Same as with other branches. I only need the rank of category for each branch_code_id.

I can't figure out how to loop this one. Rank will be placed in the "r" column as you can see in the excel output.

By the way, here's the sql statement i used to get the result you see in the screenshot.

SELECT 
    a.id, 
    a.date, 
    a.branch_code_id, 
    SUM(b.amount), 
    c.category 
FROM 
    sales_add_h AS a 
    INNER JOIN sales_add_i AS b ON a.id = b.sales_h_id 
    INNER JOIN control_panel_item_create AS c ON b.item_code_id = c.id 
GROUP BY c.category, a.branch_code_id, b.amount 
ORDER BY SUM(b.amount) DESC

Thanks Guys!

Answer 1

Try this query

SELECT 
  @rn:=if(@prv=branch_code_id, @rn+1, 1) as rId, 
  @prv:= branch_code_id as branch_code_id,
  val,
  id, 
  date, 
  category 
FROM
  (SELECT 
    a.id, 
    a.date, 
    a.branch_code_id, 
    SUM(b.amount) as val, 
    c.category 
  FROM 
    sales_add_h AS a 
  INNER JOIN 
    sales_add_i AS b ON a.id = b.sales_h_id 
  INNER JOIN 
    control_panel_item_create AS c ON b.item_code_id = c.id 
  GROUP BY 
    c.category, a.branch_code_id, b.amount 
  ORDER BY 
    a.branch_code_id, SUM(b.amount) DESC)tmp
  JOIN 
    (SELECT @rn:=0, @prv:=0)t

SQLFIDDLE to understand how ranking works.

I have done ranking for each branch_id as you have mentioned, if you want to rank for each category in a particular branch than you need to add another variable which stores the category and compare it within the if clause and also need to sort data within inner query accordingly order by c.category, a.branch_code_id, SUM(b.amount) DESC