CODEWITHSUNDEEP
mysqlsql
The Dead Man
The Dead Man

Reputation: 5566

display data from database using sql

I have events table, now I want to display the most frequent element in a column using SQL

Here is the event table.

id  |  selectedcountries
0       | Tanzania
1       | Tanzania
2       | Tanzania
3       | Kenya
4       | Uganda
5       | Uganda
6       | Kenya
7       | Uganda
8       | Tanzania
8       | Poland
9       | Poland
10      | Tanzania

UPDATE

For example, for this table it should return Tanzania since it is the most frequent value:

Here is my solution

SELECT  selectedcountries, COUNT(  'selectedcountries' ) AS  'country'
FROM EVENTS
GROUP BY  'selectedcountries'
ORDER BY  'country' DESC

Unfortunately, I am getting the following

selectedcountries country
                      73

enter image description here

What do I need to do to get what I want?

Upvotes: 0

Views: 45

Answers (2)

mkRabbani
mkRabbani

Reputation: 16908

Try this-

SELECT  selectedcountries, 
COUNT(selectedcountries) AS  'country'
FROM EVENTS 
WHERE selectedcountries <> ''
AND selectedcountries IS NOT NULL
GROUP BY  selectedcountries
ORDER BY  COUNT(selectedcountries) DESC

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270301

This is called the mode in statistics.

You can use group by and limit if you want a single value:

select selectedcountries
from events
group by selectedcountries
order by count(*) desc
limit 1;

Here is a db<>fiddle.

This does not return multiple values when there are ties. One way to get all values for ties is two levels of aggregation:

select group_concat(selectedcountries)
from (select selectedcountries, count(*) as cnt
      from events
      group by selectedcountries
     ) t
group by cnt
order by cnt desc
limit 1;

Upvotes: 1

Related Questions