List:General Discussion« Previous MessageNext Message »
From:Simcha Younger Date:February 2 2011 2:51pm
Subject:Re: Help with query.
View as plain text  
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>
Thread
Help with query.Paul Halliday1 Feb
  • Re: Help with query.Joerg Bruehe2 Feb
  • Re: Help with query.Simcha Younger2 Feb