CODEWITHSUNDEEP
phpmysqlsql
Rafael Schneider Pereira
Rafael Schneider Pereira

Reputation: 19

Generating statistics using PHP and SQL

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?

Upvotes: 0

Views: 719

Answers (1)

Nigel Ren
Nigel Ren

Reputation: 57121

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.

Upvotes: 1

Related Questions