CODEWITHSUNDEEP
mysql
KOREAN_DEVELOPER
KOREAN_DEVELOPER

Reputation: 141

How to use MySQL and count and rank

I'm a MS-SQL Developer, now I use this query (MySQL) ↓

SELECT A.place_idx,A.place_id,B.TODAY_CNT,C.TOTAL_CNT FROM CUSTOM_LIST 

AS A

INNER JOIN
(SELECT place_id,COUNT(place_id) AS TODAY_CNT from COUNT_TABLE where DATE(place_date) = DATE(NOW()) GROUP BY place_id)
AS B ON B.place_id=A.place_id

INNER JOIN
(SELECT place_id,COUNT(place_id) AS TOTAL_CNT from COUNT_TABLE GROUP BY place_id)
AS C ON C.place_id=A.place_id

The result is:

enter image description here

I want this:

enter image description here

Upvotes: 9

Views: 1337

Answers (2)

RL89
RL89

Reputation: 1916

Updated

RANK() OVER (ORDER BY TOTAL_CNT DESC DESC) AS Rank

Here I got Another Very Good Solution.:

SELECT A.place_idx,A.place_id,B.TODAY_CNT,C.TOTAL_CNT, RANK() OVER (ORDER BY TOTAL_CNT DESC) AS Rank FROM CUSTOM_LIST 

AS A

INNER JOIN
(SELECT place_id,COUNT(place_id) AS TODAY_CNT from COUNT_TABLE where DATE(place_date) = DATE(NOW()) GROUP BY place_id)
AS B ON B.place_id=A.place_id

INNER JOIN
(SELECT place_id,COUNT(place_id) AS TOTAL_CNT from COUNT_TABLE GROUP BY place_id)
AS C ON C.place_id=A.place_id

Upvotes: -1

PiTheNumber
PiTheNumber

Reputation: 23542

Try somethink like this:

SELECT ..., C.TOTAL_CNT, (@r := @r + 1) AS rank FROM CUSTOM_LIST, (SELECT  @r := 0) t
...
ORDER BY C.TOTAL_CNT DESC

Whole query:

SELECT A.place_idx,A.place_id,B.TODAY_CNT,C.TOTAL_CNT, (@r := @r + 1) AS rank 
FROM CUSTOM_LIST AS A, (SELECT  @r := 0) t

INNER JOIN
(SELECT place_id,COUNT(place_id) AS TODAY_CNT from COUNT_TABLE where DATE(place_date) = DATE(NOW()) GROUP BY place_id)
AS B ON B.place_id=A.place_id

INNER JOIN
(SELECT place_id,COUNT(place_id) AS TOTAL_CNT from COUNT_TABLE GROUP BY place_id)
AS C ON C.place_id=A.place_id

ORDER BY C.TOTAL_CNT DESC

What if we got two same values in Total_CNT?

Maybe something like this:

SELECT ..., (@last := C.TOTAL_CNT) AS TOTAL_CNT, 
  IF(@last = C.TOTAL_CNT, @r, @r := @r + 1) AS rank
FROM CUSTOM_LIST, (SELECT  @r := 0, @last := -1) t
...

Upvotes: 4

Related Questions