0

I run a SQL database with some user information. On the main page, I would like to throw some statistics about the database, and what I thought was easy at first, showed to be complicated for me (I'm a newbie).

To give a pratical example of what I'm trying to achieve, I will use a real situation to exemplify:

On my CLIENTS table, all of my clients are from different countries (represented by a country code). One of the statistics I'm trying to show, is WHAT COUNTRY HAS MORE CLIENTS.

Is there a simple way to find this kind of information? I understand I can simply count how many occurences of certain country I have on the TABLE, but I would need to compare with every country to check which on hosts more clients.

I guess that sums up my question.

EDIT: I came up with a solution but I'm just not sure if it's best, using PHP. I did a loop test for each country checking the number of clients, and compared to the one before. If the count was higher, I updated the $higher_country var, if not, I just moved to next country. Would that be my only option?

Stefan Zobel
  • 3,182
  • 7
  • 28
  • 38
Rafael Schneider Pereira
  • 19
  • 7
  • Start by describing the issue in terms that you can translate into code. Or at least a question we can answer. *What country has more clients* is not a complete expression or statement, unless you are talking about two countries that are already displayed. Know what I mean? What are you trying to do here? *Display a list of countries ordered by highest client count.*? This can be translated to code/query language. – Jacob H Feb 16 '18 at 18:06
  • I think you can use this question to solve your problem - https://stackoverflow.com/questions/12235595/find-most-frequent-value-in-sql-column – kuskmen Feb 16 '18 at 18:12

1 Answers1

1

You can do something like...

SELECT country_id, count(country_id) as nmbr 
   FROM clients 
   group by country_id 
   order by nmbr desc
   limit 1

This counts up the number of a specific value and orders it in reverse order (so highest first) and just picks the first record.

Nigel Ren
  • 56,122
  • 11
  • 43
  • 55