MYSQL Most Frequent Value Given a Certain Category

How do I find the most frequent flight type, given the Destination?

Table: Flight:
+-------------+---------------+
| Destination | Type          |
+-------------+---------------+
| Beijing     | First Class   |
| Beijing     | First Class   |
| Beijing     | First Class   |
| Beijing     | First Class   |
| Beijing     | Coach         |
| London      | Coach         |
| London      | First Class   |
| London      | Coach         |
+-------------+---------------+

Every query I've tried so far has given the total number of each flight type as opposed to the most frequent flight type for each destination.

Here is the desired output:

+-------------+--------------+-------+
| Destination | Type         | count |
+-------------+--------------+-------+
| Beijing     | First Class  |     4 |
| London      | Coach        |     2 |
+-------------+--------------+-------+

---

EDIT: So I found the easiest way was this:

SELECT destination, type, count FROM Flight WHERE total=
(
SELECT MAX(total) FROM Flight fl WHERE fl.destination = Flight.destination
);

Solved

You can try GROUP BY and HAVING. You have to select the rows which have the MAX total rows for each group (Destination, Type).

SELECT Destination, `Type`, COUNT(*) AS total
FROM your_table AS aa
GROUP BY Destination, `Type`
HAVING COUNT(*) = (
    SELECT MAX(each_total) 
    FROM (
        SELECT COUNT(*) AS each_total
        FROM your_table
        GROUP BY Destination, `Type`
    )
)
ORDER BY Destination ASC;