On Tue, 1 Feb 2011 14:46:39 -0400
Paul Halliday <paul.halliday@stripped> 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=1
>
--
Simcha Younger <simcha@stripped>
