Let's say I have this table:
+----+------+---------+
| Id | Item | Country |
+----+------+---------+
| 1 | b123 | Austria |
| 2 | a123 | Italy |
| 3 | b990 | Germany |
| 4 | h231 | Austria |
| 5 | y233 | France |
| 6 | u223 | Austria |
| 7 | p022 | Spain |
| 8 | d133 | Italy |
| 9 | w112 | Germany |
| 10 | j991 | Austria |
+----+------+---------+
I want to do a SELECT
on that table and order the results by which Country
is repeated the most.
So the expected output should be:
+----+------+---------+
| Id | Item | Country |
+----+------+---------+
| 1 | b123 | Austria |
| 4 | h231 | Austria |
| 6 | u223 | Austria |
| 10 | j991 | Austria |
| 2 | a123 | Italy |
| 8 | d133 | Italy |
| 3 | b990 | Germany |
| 9 | w112 | Germany |
| 5 | y233 | France |
| 7 | p022 | Spain |
+----+------+---------+
How can I do that?
I have tried this:
SELECT * FROM items WHERE Item != '' GROUP BY Item HAVING COUNT(*) > 1 ORDER BY COUNT(*) DESC
But that will return something like this:
+----+------+---------+
| Id | Item | Country |
+----+------+---------+
| 1 | b123 | Austria |
| 8 | d133 | Italy |
| 3 | b990 | Germany |
| 5 | y233 | France |
| 7 | p022 | Spain |
+----+------+---------+
A - Original table
B - Getting the counts at Country Level.
By joining A and B we can sort the data in descending order of count as well as display all the items from the table.
SELECT A.*
FROM items A
INNER JOIN
( SELECT Country,COUNT(*) AS cnt
FROM items
WHERE Item != ''
GROUP BY Item
) B
ON A.Country = B.Country
ORDER BY B.cnt DESC,A.Country,A.Id;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With