Find the most frequent value and order by it

Question

+-----------------------+------------------------+
| being_followed        | follower               |
+-----------------------+------------------------+
| Bob Dylan             |                      B |
| Bob Dylan             |                      A |
| Sam Cooke             |                      X |
| The Beatles           |                      Y |
| Bob Dylan             |                      M |
| Sam Cooke             |                      N |
+-----------------------+------------------------+

Now, I want to find which is the most occurring value in being_followed and then order by it. It should look somewhat like -

Bob Dylan - 3
Sam Cooke - 2
The Beatles - 1

Please don't mark this as a duplicate.

Possible duplicate of [Order by COUNT per value](http://stackoverflow.com/questions/2283305/order-by-count-per-value) — shree.pat18, Jan 06 '16 at 08:33

score 1 · Answer 1 · answered Jan 06 '16 at 08:29

1

Try below :

select being_followed , count(1) as count
from table
group by being_followed 
order by count desc ;

answered Jan 06 '16 at 08:29

minatverma

1,090
13
24

jitendra joshi · Answer 2 · 2016-01-06T08:32:54.620

0

Try This:-

select being_followed,count(*) total_followers
from table
group by being_followed
order by total_followers desc

edited Jan 06 '16 at 08:32

answered Jan 06 '16 at 08:29

jitendra joshi

677
5
18

`Follower` should not be in `group by`. It will not give proper result. Please validate . – minatverma Jan 06 '16 at 08:31
Also, `order by` should be based on `count` as per question. – minatverma Jan 06 '16 at 08:32

score 0 · Answer 3 · answered Jan 06 '16 at 08:32

SELECT being_followed , count(being_followed )as counter FROM `table_Name` GROUP BY being_followed ORDER BY counter DESC

You will get result what you want.Here using group by you will get unique value and using count you will get counter of same being_followed

score 0 · Answer 4 · answered Jan 06 '16 at 08:33

Try this:

SELECT being_followed,COUNT(*) AS follower 
FROM tablename GROUP BY being_followed ORDER BY follower DESC;

Output:

+-----------------------+------------------------+
| being_followed        | follower               |
+-----------------------+------------------------+
| Bob Dylan             |                      3 |
| Sam Cooke             |                      2 |
| The Beatles           |                      1 |
+-----------------------+------------------------+

score 0 · Answer 5 · answered Jan 06 '16 at 08:39

0

Try this

SELECT being_followed,COUNT(1) count_followers
FROM table
GROUP BY being_followed
ORDER BY COUNT(1) DESC;

Get count of being_followed and also order by high to low bases(descending order)

answered Jan 06 '16 at 08:39

Vipin Jain

3,686
16
35

score 0 · Answer 6 · answered Jan 06 '16 at 08:48

0

Perhaps you can try this:

select being_followed, count(*) follower
from TableName
group by being_followed
order by follower desc

It's working fine.

answered Jan 06 '16 at 08:48

Suvro

352
2
13

Find the most frequent value and order by it

6 Answers6