Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Mysql: Order rows by value frequency

Tags:

mysql

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   |
+----+------+---------+
like image 212
Cornwell Avatar asked Mar 10 '23 10:03

Cornwell


1 Answers

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; 
like image 164
Teja Avatar answered Mar 20 '23 06:03

Teja