MySQL - Count the maximum number of count that a row has repeated-CodePudding

I have this data in my database.

id  | company_id |     referrer
001 | 001        | https://google.com
002 | 001        | https://riot.com
003 | 001        | https://google.com
004 | 002        | https://vimeo.com
005 | 002        | https://yahoo.com
006 | 003        | https://yahoo.com

I want to get the number of times a referrer has repeated per company_id. A company has many referrer, but I only want to get the highest referrer that repeated in the data.

So the result would be something like this:

company_id | referrer           | nRepeat
001        | https://google.com |  2
002        | https://yahoo.com  |  2

I tried this code:

SELECT company_id,
       referrer,
       Count(referrer) AS viewCount
FROM   `my_table`
GROUP  BY company_id
ORDER  BY viewcount DESC

This query seems to be wrong.

Apologies if I explain it poorly, let me know if you have questions.

Any tips or help on this? Your help is greatly appreciated! Thanks!

CodePudding user response：

We can use RANK here:

WITH cte AS (
    SELECT company_id, referrer, COUNT(*) viewCount,
           RANK() OVER (PARTITION BY company_id ORDER BY COUNT(*) DESC) rnk
    FROM my_table
    GROUP BY company_id, referrer
)

SELECT company_id, referrer, viewCount
FROM cte
WHERE rnk = 1;

Here is the above written as a single select, sans the use of a CTE:

SELECT company_id, referrer, viewCount
FROM
(
    SELECT company_id, referrer, COUNT(*) viewCount,
           RANK() OVER (PARTITION BY company_id ORDER BY COUNT(*) DESC) rnk
    FROM my_table
    GROUP BY company_id, referrer
) t
WHERE rnk = 1;

Here is a version which should work on MySQL versions earlier than 8 :

SELECT company_id, referrer, COUNT(*) viewCount
FROM my_table t1
GROUP BY company_id, referrer
HAVING COUNT(*) = (SELECT COUNT(*)
                   FROM my_table t2
                   WHERE t2.company_id = t1.company_id
                   GROUP BY referrer
                   ORDER BY COUNT(*) DESC
                   LIMIT 1);