Get the most often occurring year

Question

I have the following table from a factory database called world in MySQL Workbench:

Table: country

Relevant Columns:

Code char(3) PK,
IndepYear smallint(6)

I'd like to get the year (or possibly years) when the most countries have become independent. I don't want to list anything else just the year(s), one row per year.

EDIT:

I have found the right solution that actually works.

SELECT IndepYear
FROM   (SELECT IndepYear,
               COUNT(IndepYear) AS Years
        FROM   world.country
        GROUP  BY IndepYear
        ORDER  BY Years DESC) AS T1
WHERE  Years IN (SELECT Years
                 FROM   (SELECT IndepYear,
                                COUNT(IndepYear) AS Years
                         FROM   world.country
                         GROUP  BY IndepYear
                         ORDER  BY Years DESC
                         LIMIT  1) AS T2
                 GROUP  BY IndepYear)
ORDER  BY IndepYear ASC;

Note that the number in parentheses after an integer type is almost entirely meaningless — Strawberry, Aug 27 '18 at 22:08

score 1 · Answer 1 · answered Aug 27 '18 at 22:08

1

It sounds like you just want to ORDER BY the COUNT of IndepYear. Don't forget that you'll also want to GROUP BY this column, along with adding a LIMIT of 1 to get the most frequently occuring year when sorting it in descending order.

SELECT   `IndepYear`,
         COUNT(`IndepYear`) AS `CommonYear` 
FROM     `country`
GROUP BY `IndepYear`
ORDER BY `CommonYear` DESC
LIMIT    1;

answered Aug 27 '18 at 22:08

Obsidian Age

41,205
10
48
71

It only works for one year, if there is two or more years occurring the same times it wont list them all. – sasieightynine Aug 27 '18 at 22:20

score 1 · Answer 2 · answered Aug 27 '18 at 22:23

Use aggregation to get the count of each year and limit it to the maximum count in a HAVING clause. Use a subquery to get the maximum count, aggregating the aggregation.

SELECT indepyear
       FROM country
       GROUP BY indepyear
       HAVING count(*) = (SELECT max(c)
                                 FROM (SELECT count(*) c
                                              FROM country
                                              GROUP BY indepyear) x);

SQL Fiddle

Get the most often occurring year

2 Answers2