CODEWITHSUNDEEP
mysqlsqldatabase
rubikskube
rubikskube

Reputation: 402

Selecting all the similar MAX values derived from COUNT()

I have a simple MySQL table looks this:

    Table: Citizen

    Name    Country
    ----    ------
    Adam    USA
    Eva     Russia
    Arman   India
    Peter   USA
    Martin  Russia
    Jack    Finland
    Juno    Russia
    Fox     USA
    Maya    Finland

A simple query like

SELECT Country, COUNT(Name) People FROM Citizen  GROUP BY Country

will give me

Country     People
-------     ------
USA         3
Russia      3
Finland     2
India       1

I want to find the Country with MAXIMUM number of people. In the case of a tie (i.e if two countries have the same number of max people), list all such country. The result table should look like this

Country     MaxPeople
-------     ------
USA         3
Russia      3

I learned something about derived tables and came up with this query:

SELECT Country, MAX(TotCit) 
FROM (SELECT Country, COUNT(People) TotCit 
      FROM Citizen GROUP BY Country) 
AS CitTable

Which results in

Country   Max(TotCit)
-------   -----------
Finland   3

Any possible solution?

Upvotes: 0

Views: 71

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115630

The query by Gordon, simplified a bit.

  • you don't need COUNT(Name), unless Nname is a nullable column.

  • the 2-nested subquery can be replaced with a simple subquery with LIMIT:

    SELECT Country, COUNT(*) AS People
FROM Citizen 
GROUP BY Country
HAVING COUNT(*) = 
       ( SELECT COUNT(*) AS People
         FROM Citizen
         GROUP BY Country
         ORDER BY People DESC
         LIMIT 1
       ) ;

The condition in HAVING will probably lead to a very inefficient query, so you could rewrite as:

SELECT 
    g.Country, g.People
FROM
  ( SELECT COUNT(*) AS People
    FROM Citizen
    GROUP BY Country
    ORDER BY cnt DESC
    LIMIT 1
  ) AS m
  JOIN
  ( SELECT Country, COUNT(*) AS People
    FROM Citizen  
    GROUP BY Country
  ) AS g
      ON g.People = m.People ;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

If you need all the countries, then the query is a bit cumbersome:

SELECT Country, COUNT(Name) as People
FROM Citizen 
GROUP BY Country
HAVING COUNT(Name) = (select max(cnt)
                      from (select COUNT(Name) as cnt
                            from Citizen
                            group by Country
                           ) c
                     );

The query does just what you are looking for. The HAVING clause first calculates the counts for each country, then it calculates the max() and compares that to the aggregated value.

By the way, this is much easier in most other databases because they support window/analytic functions.

Upvotes: 3

Related Questions