On Tue, 1 Feb 2011 14:46:39 -0400 Paul Halliday <paul.halli...@gmail.com> 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; >..... > All I am interested in is the event count for each country, in this case: > > 295 CN > ... Other countries.. try: SELECT COUNT(*) AS count, IF(map1.cc, map1.cc, map2.cc) AS country 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 country ORDER BY country note, I am assuming in this query that you have either a source country or a destination country, but not both. If both values might be set, and you need to count each, this will not work. > 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=sim...@syounger.com > -- Simcha Younger <sim...@syounger.com> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
