Search code examples
mysqlsummary

How to get maximum number of distinct values for multiple columns in MYSQL

I am trying to find the number of ip/port combinations used by users of an online system. I want to create columns with the maximum number of ips and ports used per username.

The basic query I'm starting with is this:

Select distinct username, ip, port from users u, peers p where u.usernum=p.usernum group by username, ip, port

For data:

a 1.1.1.1 12345
a 1.1.1.1 13579
b 9.8.7.6 11111
b 9.5.5.5 11115
b 9.2.2.2 11111
c 5.6.7.8 33333
c 5.6.0.0 33333

I'd like the output to resemble the following:

user max_ip max_port
a    1      2 
b    3      2
c    2      1

I've tried various forms of grouping, but with no success. Thanks in advance for your help.

Solution 
  • SELECT username
     , COUNT(DISTINCT ip) AS max_ip
     , COUNT(DISTINCT port) AS max_port 
FROM users u
  JOIN peers p 
    ON u.usernum = p.usernum 
GROUP BY username