List:General Discussion« Previous MessageNext Message »
From:Jim Faucette Date:June 10 1999 4:46pm
Subject:Re: GROUPING BLUES (Any Suggestions :(
View as plain text  
It's because your date field is a timestamp. Look at the manual's date
functions to see how best to ignore the time part. (The (6) is just for
display purposes. The actual data contains a full date-time entry.)

  jim...

Jeremy Keith wrote:
> 
> I am having trouble grouping or maybe I am going about this all wrong.  Heres a
> sample of my table.
> 
> TABLE usagecharts
> 
>  date                  TIMESTAMP(6),
>   router                CHAR(64) NOT NULL,
>   ifInOctets    INT UNSIGNED NOT NULL,
>   ifOutOctets   INT UNSIGNED NOT NULL
> 
> --------
> 
> Ok, I have a daemon which updates in / out  for each router every hour, and stamps
> the date.  As a result I have multiple 060999, for one day, and multiple 061099 etc, for
> next day.  Heres what I get when I group it by date:
> 
> mysql> select date,router,SUM(ifInOctets),SUM(ifOutOctets) from usagecharts GROUP
> BY date;
> +--------+---------+-----------------+------------------+
> | date   | router  | SUM(ifInOctets) | SUM(ifOutOctets) |
> +--------+---------+-----------------+------------------+
> | 990610 | 1.1.1.1 |         3242434 |          3243243 |
> | 990610 | 1.1.1.1 |           12343 |            23243 |
> | 990611 | 1.1.1.1 |          624324 |           423243 |
> | 990611 | 1.1.1.1 |            4324 |           823243 |
> +--------+---------+-----------------+------------------+
> 
> What I want is the totals summed up so it will only display the following ;
> 
> +--------+---------+-----------------+------------------+
> | date   | router  | SUM(ifInOctets) | SUM(ifOutOctets) |
> +--------+---------+-----------------+------------------+
> | 990610 | 1.1.1.1 |         3365864 |          3262543 |
> | 990611 | 1.1.1.1 |          624324 |           423243 |
> +--------+---------+-----------------+------------------+
> 
> How can I do this?  Any suggestions :).   I only want added up totals totalled up per
> date (like above).
> 
> Thanks
Thread
GROUPING BLUES (Any Suggestions :(Jeremy Keith10 Jun
  • Re: GROUPING BLUES (Any Suggestions :(Jim Faucette10 Jun