Mysql Ranking based on Category and Branch
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:
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!
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
下一篇: 基于分类和分支的Mysql排名