> Thanks a lot for your suggestion, that in fact gets me
> through the loop.
> Only problem is, I need to group by origine and get that
> sum(bytes) and by
> destinazione and get that sum(bytes). They are different.
That's why I self join to the 'day' table and sum(o.bytes) and
sum(d.bytes), which should give you different results if origine and
destinazione aren't always the same on each row.
> Your statement
> only groups for origine (in fact it groups by address, which
> is non-existent
> in the day table, and gets me null values).
You are correct that should have been 'o.origine', sorry I was cut and
pasting your sql and missed that replace. But the query should work. if
you make that one change.
Corrected query:
INSERT INTO week (address, origbytes, destbytes, when)
SELECT o.origine, sum(o.bytes), sum(d.bytes), '09/02/1999 9:00:01'
FROM day o, day d
WHERE o.origine=d.destinazione AND
o.origine << '194.74.133.0/24' AND
o.when BETWEEN '09/02/1999 9:00:01' AND '09/02/1999 9:59:59' AND
d.when BETWEEN '09/02/1999 9:00:01' AND '09/02/1999 9:59:59'
GROUP BY o.origine, 4;
> In the meantime, I have discovered that the number of queries
> is irrelevant
> to the crash, the crash happens in the INSERT statement when
> the datetime
> field is set to 12:00:00 or 12:59:59 or whatever, but always at 12.
>
> Ludo
Rerun your loop and print out your date as you go until you get the
crash, and send it in. You might actually want to send in the perl-code
in question as well.
BTW. Your query will miss anything that happens on the hour.
-DEJ