Hi Paul!
Paul Halliday wrote: > 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.. As a first step, remove the columns "src_ip" and "dst_ip" from your query, both from the select list and from the "group by" (but not from the join condition): SELECT COUNT(signature) AS count, map1.cc as src_cc, 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_cc, dst_cc ORDER BY src_cc, dst_cc ASC; The result should be: +-------+--------+--------+ | count | src_cc | dst_cc | +-------+--------+--------+ | 8 | NULL | CN | | 287 | CN | NULL | +-------+--------+--------+ Now, you are left with two problems: 1) Your query still groups by the country codes of both source and destination (which doesn't become obvious with your sample data, as one of these is always shown as NULL). For example: traffic just between three countries (each combination occurring) would give nine rows, with each country occurring three times as source and three times as destination. If you want the total for the source country, you must stop grouping by destination (and vice versa). 2) If you really want the total of source and destination (the 295 in your example, not the 287 and 8 I expect from my version), it really might be easiest to do this in the application; I have no idea how to do it nicely in SQL. HTH, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@oracle.com ORACLE Deutschland B.V. & Co. KG, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven Amtsgericht Muenchen: HRA 95603 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
