title: Aggregating multiple groups in SQL
body: <p>I have an application that needs to query a MySQL database and retrieve a list of users that may be sharing an IP address, and I am having some trouble turning my the concept of what I want to do with my query in my head into a functional query.</p>
  
  <p>The situation is that I have a table which contains known ip information for users. Each time the user logs in, it creates a timestamped entry containing their user id and the ip address they logged into.</p>
  
  <p>Initially, I used the following query to return rows representing IP addresses that were shared:</p>
  
  <pre><code>select ip, GROUP_CONCAT(DISTINCT account ORDER BY timestamp SEPARATOR ' ')
  from known_ips
  group by ip having count(1) &gt; 1
  </code></pre>
  
  <p>However, many users have dynamic IP addresses so this list contains many duplicate entries (one for each IP address they share with others, obviously).</p>
  
  <p>What I would like to do is have each row returned be a unique group of users that have shared any ip address with one another at any point.</p>
  
  <p>For example, if Bob and Jane had shared IP address 192.168.0.1 and Bob and Fred had shared IP address 192.168.0.2, I would want the row to return 'Bob Fred Jane' (the program is taking the results of this query and doing some operations with it, and essentially needs a list of accounts on which to take action).</p>
  
  <p>What I can't figure out on my own is how to do this aggregation (or whether it is even possible). I initially tried to have the original query as CTE (using with clause) and then trying to group on that, however I reached a stumbling block in that I couldn't figure out how to logically carry out the operation "compare string-delimited list of users in group 1 to see if any exist in group 2", and I figure doing that sort of string comparison is not what SQL is all about anyway (and I can do it in the program rather than the SQL besides).</p>
  
  <p>Does anyone know of any technique whereby I can represent the logic of what I am trying to achieve here in MySQL? Or, should I accept the solution I already reached and then do the aggregation in the client application?</p>
  
  <p><strong>Edit:</strong></p>
  
  <p>In response to the request to a sample of data and output, here is a contrived example of the data:</p>
  
  <pre><code>Account     IP       Timestamp
  Bob     192.168.0.1  2014-02-12 08:00
  Bob     192.168.0.1  2014-02-12 09:30
  Bob     192.168.0.2  2014-02-12 10:00
  Mary    192.168.0.1  2014-03-12 07:00
  Bob     192.168.0.2  2014-03-12 08:00
  Jim     192.168.0.4  2014-03-12 08:30
  Ted     192.168.0.2  2014-03-12 09:00
  Jim     192.168.0.5  2014-04-12 08:30
  Bob     192.168.0.3  2014-04-12 09:30
  Andy    192.168.0.6  2014-04-12 10:30
  Paul    192.168.0.6  2014-04-12 11:30
  </code></pre>
  
  <p>From this sample data, I would expect exactly two rows returned:</p>
  
  <pre><code>Bob Mary Ted
  Andy Paul
  </code></pre>
  
  <p>I am ambivalent about the ordering of accounts in the list, despite my use of ORDER BY timestamp earlier.</p>