CODEWITHSUNDEEP
mysql
Laz Karimov
Laz Karimov

Reputation: 714

Get frequency of data from two columns via one SQL query

I have the table
_______________ | from | to | | 1 | 2 | | 2 | 2 | | 1 | 2 | | 3 | 2 |
Where from and to are countries.
I need to get top 10 countries met in from and to.

The result would be
________________ |country| count | | 2 | 5 | | 1 | 2 | | 3 | 1 |
I reached what I need in two queries, but I am almost sure there is possibility to manage it with one.
Thanks in advance.

Upvotes: 0

Views: 583

Answers (1)

Will
Will

Reputation: 2880

You want to use a UNION ALL and then GROUP BY using the union of from and to: 

SELECT country, COUNT(*) AS count FROM
  (SELECT from AS country FROM table_name
  UNION ALL
  SELECT to AS country FROM table_name)
GROUP BY country 
ORDER BY count DESC
LIMIT 10

Upvotes: 2

Related Questions