Getting the most common value in a table-CodePudding

I'm struggling to work out how to get the most commonly occurring value from a table in MySQL. Example:

CREATE TABLE words(`letter1` char(1), `letter2` char(1));
    
INSERT INTO words(`letter1`, `letter2`)VALUES
    ('A', 'A'),    ('B', 'A'),    ('C', 'A'),    ('D', 'A'),    ('D', 'B'),
    ('B', 'B'),    ('D', 'B'),    ('A', 'C'),    ('B', 'D'),    ('D', 'A');

So for letter1 I want to pick out the value 'D' and for letter2 I want to pick out 'A'. For a tie I'm not too bothered which of the tied values it picks. Thanks for any help, It looks like it ought to be easy but I can't figure it out. For one letter it would be easy but for multiple I don't know how to.

CodePudding user response：

SELECT ( SELECT letter1
         FROM table
         GROUP BY 1
         ORDER BY COUNT(*) DESC LIMIT 1 ) letter1,
       ( SELECT letter2
         FROM table
         GROUP BY 1
         ORDER BY COUNT(*) DESC LIMIT 1 ) letter2;

If two or more letters have the same and maximal amount of occurences then one indefinite letter of these letters (but in most cases - the least lexicographically) will be returned.

CodePudding user response：

Will it work for you?

Query for letter1

SELECT letter1 FROM words GROUP BY letter1 ORDER BY COUNT(1) DESC LIMIT 1;

Query for letter2

SELECT letter2 FROM words GROUP BY letter2 ORDER BY COUNT(1) DESC LIMIT 1;

CodePudding user response：

For calculations based on individual columns, Akina already provided an answer. Just in case you'd like to find the mostly used pair , you can try this if you don't care which pair to pick in case of a tie:

select count(*) ct,letter1,letter2 from words group by letter1,letter2 order by ct desc limit 1;