MySQL: Rank the total total amount with COUNT()

user_13766268 0
Viewed 138

Edit: I want to rank with COUNT()

I've made a View with every order a customer made. In the next step I wrote a query to calculate the total amount a customer purchased. Now I want to rank the customers, based on their total purchase.

I wrote this query:

SELECT u.m_name, SUM(u.num * u.price) AS total,
(SELECT COUNT(*)
FROM v_sales AS x
WHERE x.m_id = u.m_id
AND (SELECT SUM(s1.num * s1.price) FROM v_sales AS s1 WHERE s1.m_id = x.m_id)
>
(SELECT SUM(s2.num * s2.price) FROM v_sales AS s2 WHERE s2.m_id = x.m_id)
) + 1 AS Rank
FROM v_sales AS u
GROUP BY u.m_id;

But the results are not the expected ones:

# m_name   total   Rank
川島智弘    2620    1
河田英毅    0       1
山田忠明    15420   1
永峰弘万    500     1
永山智広    380     1

I need the following output:

# m_name    total   Rank 
川島智弘     2620    2
河田英毅     0       5
山田忠明     15420   1
永峰弘万     500     3
永山智広     380     4

Has someone an idea what I did wrong? It would be also helpful if someone could explain why my query doesn't work. Here is a Fiddle

Thank you

rank count view mysql
1 Answers

You can use the RANK function in MariaDB 10.4.

SELECT m_name, SUM(num * price) AS total,
 RANK() OVER(ORDER BY SUM(num * price) DESC)
FROM v_sales
GROUP BY m_id;

Fiddle

No window function:

SELECT t1.m_name,MAX(t1.total),COUNT(t2.m_name)+1 as RANK
FROM
(SELECT m_name, SUM(num * price) AS total FROM
v_sales
GROUP BY m_id) t1
LEFT JOIN
(SELECT m_name, SUM(num * price) AS total FROM
v_sales
GROUP BY m_id) t2
ON t1.total<t2.total
GROUP BY t1.m_name
ORDER BY 3

Fiddle

user_138410160
Related