How can I group by the total number of occurrence in MySQL?-CodePudding

Assume my database structure looks something. like this

$ SELECT ip, account_id from logs LIMIT 10;
 ----------------- ------------ 
| ip              | account_id |
 ----------------- ------------ 
| 62.171.147.99   |     160936 |
| 161.97.93.125   |     160936 |
| 116.212.150.204 |     181860 |
| 119.160.117.4   |       NULL |
| 119.160.117.4   |       NULL |
| 119.160.117.4   |       NULL |
| 116.212.150.204 |     181860 |
| 116.212.150.204 |     181860 |
| 116.212.150.204 |     181860 |
| 161.97.93.125   |     160936 |
 ----------------- ------------

IP and account_id is repeatable, and an account_id can be associated with multiple IPs and another way around.

I want to write a query that shows me the IP and the total number of users associated with the IP. Later I want to modify it to get the IP related to x number of users. Any pointers would be highly appreciated.

I tried this query but it does not seems to be giving me the exact results.

SELECT account_id, ip, count(*) FROM logs GROUP BY account_id LIMIT 10;

Thanks you

CodePudding user response：

SELECT IP,COUNT(account_id )AS  total_number_of_users_associated_with_the_IP
FROM LOGS
GROUP BY IP

  SELECT IP,COUNT(DISTINCT account_id )AS  total_number_of_users_associated_with_the_IP
   FROM LOGS
   GROUP BY IP