I have a query (thanks to this list) that uses a join to add country information to an IP. It looks like this:
SELECT COUNT(signature) AS count, INET_NTOA(src_ip), map1.cc as src_cc, INET_NTOA(dst_ip), map2.cc as dst_cc FROM event LEFT JOIN mappings AS map1 ON event.src_ip = map1.ip LEFT JOIN mappings AS map2 ON event.dst_ip = map2.ip WHERE timestamp BETWEEN '2011-01-29 00:00:00' AND '2011-01-30 00:00:00' GROUP BY src_ip, src_cc, dst_ip, dst_cc ORDER BY src_cc, dst_cc ASC; This would return something like this: +-------+-------------------+--------+-------------------+--------+ | count | INET_NTOA(src_ip) | src_cc | INET_NTOA(dst_ip) | dst_cc | +-------+-------------------+--------+-------------------+--------+ | 8 | 10.0.0.8 | NULL | 61.55.142.129 | CN | | 1 | 210.52.216.92 | CN | 10.0.0.2 | NULL | | 1 | 121.33.205.235 | CN | 172.16.0.6 | NULL | | 239 | 210.52.216.92 | CN | 10.0.0.2 | NULL | | 2 | 121.33.205.235 | CN | 172.16.0.15 | NULL | | 4 | 121.33.205.235 | CN | 10.0.0.1 | NULL | | 39 | 210.52.216.92 | CN | 172.16.0.15 | NULL | | 1 | 121.33.205.235 | CN | 172.16.0.14 | NULL | +-------+-------------------+--------+-------------------+--------+ All I am interested in is the event count for each country, in this case: 295 CN ... Other countries.. I can do this in code, more work of course, but I am just curious if I can pull it off with a single query. Thanks! -- Paul Halliday http://www.pintumbler.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
