display data from database using sql

Question

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

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

I think the only problem is the quotation marks in your GROUP BY clause. — workerjoe
– workerjoe, Commented Aug 6, 2019 at 11:59

Gordon Linoff · Accepted Answer · 2019-08-06 12:18:32Z

1

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;

edited Aug 6, 2019 at 12:18

answered Aug 6, 2019 at 11:57

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

The Dead Man Over a year ago

@Strawberry your right, this does not work at all, return empty

Gordon Linoff Over a year ago

@user9964622 . . . The code does work. That is not what Strawberry is referring to. Given your sample queries, you are probably confused by how to use quotes in SQL queries.

The Dead Man Over a year ago

@GordonLinoff i tested just returns empty column

Gordon Linoff Over a year ago

@user9964622 . . . Then the empty value has the highest count. I added a db<>fiddle as a demonstration.

The Dead Man Over a year ago

Your right, its working , I have one more question assume I have other multiple null values inside column selectedcountry I dont want these null or empty values to be counted, what do I need to add in that sql statement your provided in a fiddle?

|

mkRabbani · Accepted Answer · 2019-08-06 12:26:55Z

1

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

edited Aug 6, 2019 at 12:26

answered Aug 6, 2019 at 11:57

mkRabbani

16.9k2 gold badges17 silver badges26 bronze badges

5 Comments

mkRabbani Over a year ago

check fiddle - dbfiddle.uk/…

mkRabbani Over a year ago

You can apply limit 1 if you need the first row only.

The Dead Man Over a year ago

aah got you, for example if I have empty column and I dont want them to be counted what do I need to add in my statement?

mkRabbani Over a year ago

Add WHERE selectedcountries <> '' before GROUP BY. Check updated script.

mkRabbani Over a year ago

Added both Empty and NULL checking. This should work for all case.

Collectives™ on Stack Overflow

display data from database using sql

2 Answers 2

6 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related