
Just trying to summarize some traffic stats, and am either running the
query wrong, or you can't do this?

The query is:

  SELECT CASE WHEN to_ip << '216.126.84.0/24' THEN to_ip ELSE from_ip END AS LocalAddr,
         sum(bytes) as TotalBytes, date_trunc('day', runtime) AS Day
    FROM stat_log
   WHERE date_trunc('day', runtime) = '2001-01-02 00:00:00-05'
GROUP BY LocalAddr, Day;

returns:

    localaddr    | totalbytes |          day
-----------------+------------+------------------------
 24.6.125.174    |      13716 | 2001-01-02 00:00:00-05
 24.43.137.113   |      13140 | 2001-01-02 00:00:00-05
 24.128.201.128  |      14376 | 2001-01-02 00:00:00-05
 64.39.38.43     |      14232 | 2001-01-02 00:00:00-05
 128.11.44.16    |      25050 | 2001-01-02 00:00:00-05
 130.149.17.13   |      14316 | 2001-01-02 00:00:00-05
 142.177.197.180 |     179676 | 2001-01-02 00:00:00-05
 151.164.30.54   |      13260 | 2001-01-02 00:00:00-05
 166.84.192.39   |      13614 | 2001-01-02 00:00:00-05
 192.67.198.32   |      13872 | 2001-01-02 00:00:00-05
 192.245.12.7    |      14676 | 2001-01-02 00:00:00-05
 193.228.80.12   |      13092 | 2001-01-02 00:00:00-05
 194.126.24.131  |      21642 | 2001-01-02 00:00:00-05
 194.209.182.36  |      14448 | 2001-01-02 00:00:00-05
 195.46.202.129  |      73518 | 2001-01-02 00:00:00-05
 195.117.86.253  |      13056 | 2001-01-02 00:00:00-05
 196.38.110.24   |      15012 | 2001-01-02 00:00:00-05
 202.160.254.40  |      38178 | 2001-01-02 00:00:00-05
 207.123.82.5    |      15240 | 2001-01-02 00:00:00-05
 207.136.80.247  |      25290 | 2001-01-02 00:00:00-05
 208.158.96.110  |      17940 | 2001-01-02 00:00:00-05
 209.47.145.10   |    2881400 | 2001-01-02 00:00:00-05
 209.47.148.2    |    3263955 | 2001-01-02 00:00:00-05
 209.223.182.2   |     222180 | 2001-01-02 00:00:00-05
 212.43.217.25   |      22974 | 2001-01-02 00:00:00-05
 216.126.72.6    |    1265472 | 2001-01-02 00:00:00-05
 216.126.72.30   |      94615 | 2001-01-02 00:00:00-05
 216.126.84.1    |  201733744 | 2001-01-02 00:00:00-05
 216.126.84.10   |     151665 | 2001-01-02 00:00:00-05
 216.126.84.11   |     103630 | 2001-01-02 00:00:00-05
 216.126.84.14   |     752305 | 2001-01-02 00:00:00-05

Yet:

select * from stat_log_holding where from_ip << '216.126.84.0/24';

returns what I'd expect:

    from_ip     |      to_ip      | port |  bytes   |        runtime
----------------+-----------------+------+----------+------------------------
 216.126.84.1   | 212.7.160.126   |  873 | 16091760 | 2001-01-16 10:53:14-05
 216.126.84.28  | 195.176.0.212   |   80 | 10247530 | 2001-01-16 10:53:14-05
 216.126.84.73  | 193.172.127.85  |   80 |  7856477 | 2001-01-16 10:53:14-05
 216.126.84.73  | 195.149.181.21  |   80 |  6343572 | 2001-01-16 10:53:14-05
 216.126.84.1   | 216.126.84.253  |   53 |  4401161 | 2001-01-16 10:53:14-05
 216.126.84.28  | 195.230.44.100  |   80 |  3157811 | 2001-01-16 10:53:14-05
 216.126.84.95  | 194.206.159.140 |   80 |  3140439 | 2001-01-16 10:53:14-05


So, am I doing something wrong here, as far as that CASE statement is
concerned, or is this a bug in v7.0.3 that is fixed in v7.1?

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org



Tue, 16 Jan 2001 14:39:56 -0400 (AST)
The Hermit Hacker <scrappy@hub.org>
CASE inet << inet ... 
The Hermit Hacker <scrappy@hub.org> writes:
> Just trying to summarize some traffic stats, and am either running the
> query wrong, or you can't do this?

I can't tell if there's anything wrong with that or not.  You didn't
show us the input data being used by the CASE expression ...

			regards, tom lane

Tue, 16 Jan 2001 19:09:42 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: CASE inet << inet ... 

ah shit, trying to come up with an example, I figured out what I did ...
some of the records don't fall into that range, so of course, to_ip isn't,
so it just displays teh fron_ip *sigh*

I'm going back to sleep now ... :(


On Tue, 16 Jan 2001, Tom Lane wrote:

> The Hermit Hacker <scrappy@hub.org> writes:
> > Just trying to summarize some traffic stats, and am either running the
> > query wrong, or you can't do this?
>
> I can't tell if there's anything wrong with that or not.  You didn't
> show us the input data being used by the CASE expression ...
>
> 			regards, tom lane
>

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org


Tue, 16 Jan 2001 21:22:42 -0400 (AST)
The Hermit Hacker <scrappy@hub.org>
Re: CASE inet << inet ... 