CODEWITHSUNDEEP
mysql
japem
japem

Reputation: 1121

Select most frequent value from a group by function - MySQL

I'm using Sequel Pro on a Mac and have a table like this:

Table1
a        b
Blah     5
Blah     5
Smoog    5
Choop    7
Choop    7
Choop    7
Smap     7
Smap     7
Tweep    11
Tweep    11
Florg    11

And I'm running this query:

Select "MOST FREQUENT OCCURRENCE"(a), b 
From Table1
Group by b

What would be the operator I put in for the "MOST FREQUENT OCCURRENCE" part? Basically what I am trying to do is figure out which "a" occurs most often with each "b". The output should look like:

"MOST FREQUENT OCCURRENCE"(a)    b
Blah                             5
Choop                            7
Tweep                            11

Thank you!

Upvotes: 1

Views: 588

Answers (1)

abl
abl

Reputation: 5968

Not the prettiest query I've seen, but it works. Keep in mind that it can be slow for large sets of data. Any kind of count typically is.

SELECT cnts1.a AS a, cnts1.b AS b FROM 
(SELECT a, b, COUNT(*) cnt 
        FROM Table1 t1 
        GROUP BY a, b
      ) AS cnts1
LEFT JOIN
(SELECT a, b, COUNT(*) cnt 
        FROM Table1 t1 
        GROUP BY a, b
      ) AS cnts2
ON cnts1.b = cnts2.b
AND cnts1.cnt < cnts2.cnt
WHERE cnts2.a IS NULL

Edit:

For the very duplication-unfriendly there is this other alternative which performs the counting only once. Instead of calculating the count of occurrences for each a, then seeing which a has the maximum count, it calculates for each a a string which is composed of the count of occurrences plus the value of a itself. After that you get the maximum string, from which the value of a is extracted.

Since the count must be padded with zeroes when constructing the string there is a limit to the maximum amount of occurrences that this query can handle, in the example being 99999 (padding to 5 digits).

SELECT SUBSTRING(cnta, 6) AS a, b
FROM (SELECT b, MAX(cnta) cnta
      FROM (SELECT b, CONCAT(LPAD(COUNT(*), 5, '0'), a) cnta
            FROM table1
            GROUP BY a, b
            ) cnts
      GROUP BY b
      ) maxcnts

Upvotes: 2

Related Questions