Almost. I'm sure that'll be needed in there someplace.....
here's what I've got:
CREATE TABLE `readings` (
`date` timestamp(14) NOT NULL,
`speed` float NOT NULL default '0',
`ip` text NOT NULL,
`name` text NOT NULL
) TYPE=MyISAM;
#
# Dumping data for table `readings`
#
INSERT INTO `readings` VALUES (20040116022451, '199.9', '68.66.221.27',
'68-66-221-27.vnnyca.adelphia.net');
INSERT INTO `readings` VALUES (20040116024016, '2427.1', '68.66.221.27',
'68-66-221-27.vnnyca.adelphia.net');
INSERT INTO `readings` VALUES (20040116024725, '3356.9', '68.66.221.27',
'68-66-221-27.vnnyca.adelphia.net');
INSERT INTO `readings` VALUES (20040116025421, '3375.8', '68.66.221.27',
'68-66-221-27.vnnyca.adelphia.net');
INSERT INTO `readings` VALUES (20040116030206, '3325.1', '68.66.221.27',
'68-66-221-27.vnnyca.adelphia.net');
INSERT INTO `readings` VALUES (20040116113508, '1307.1', '4.62.124.140',
'lsanca1-ar1-4-62-124-140.lsanca1.dsl-verizon.net');
INSERT INTO `readings` VALUES (20040116151930, '115.3', '68.66.221.27',
'68-66-221-27.vnnyca.adelphia.net');
INSERT INTO `readings` VALUES (20040116152651, '3187.1', '68.66.221.27',
'68-66-221-27.vnnyca.adelphia.net');
INSERT INTO `readings` VALUES (20040116195542, '3052.5', '68.66.221.27',
'68-66-221-27.vnnyca.adelphia.net');
INSERT INTO `readings` VALUES (20040116214644, '99.8', '68.66.221.27',
'68-66-221-27.vnnyca.adelphia.net');
INSERT INTO `readings` VALUES (20040117012159, '3187.1', '68.66.221.27',
'68-66-221-27.vnnyca.adelphia.net');
INSERT INTO `readings` VALUES (20040117025246, '3311.7', '68.66.221.27',
'68-66-221-27.vnnyca.adelphia.net');
INSERT INTO `readings` VALUES (20040117025916, '3371.2', '68.66.221.27',
'68-66-221-27.vnnyca.adelphia.net');
INSERT INTO `readings` VALUES (20040117095905, '1307.5', '4.12.14.59',
'wbar4.sea-4-12-014-059.dsl-verizon.net');
INSERT INTO `readings` VALUES (20040117102846, '30.1', '198.81.26.46',
'cache-ntc-ab09.proxy.aol.com');
INSERT INTO `readings` VALUES (20040117185859, '3307.2', '68.66.221.27',
'68-66-221-27.vnnyca.adelphia.net');
INSERT INTO `readings` VALUES (20040117225834, '3056.3', '68.66.221.27',
'68-66-221-27.vnnyca.adelphia.net');
INSERT INTO `readings` VALUES (20040120154905, '1307.6', '4.62.127.164',
'lsanca1-ar1-4-62-127-164.lsanca1.dsl-verizon.net');
INSERT INTO `readings` VALUES (20040123194031, '125.8', '68.66.221.27',
'68-66-221-27.vnnyca.adelphia.net');
INSERT INTO `readings` VALUES (20040124031922, '110.9', '68.66.221.27',
'68-66-221-27.vnnyca.adelphia.net');
INSERT INTO `readings` VALUES (20040124031955, '265.8', '68.66.221.27',
'68-66-221-27.vnnyca.adelphia.net');
INSERT INTO `readings` VALUES (20040124040246, '3446.8', '68.66.221.27',
'68-66-221-27.vnnyca.adelphia.net');
Here's what I want:
adelphia.net: 3000.0 kbps average
aol.com: 5.0 kbps average
dsl-verizon.net: 1500.0 kbps average
Told you it'd be a fun one! ;)
Thanks a million,
Dan
----- Original Message -----
From: <dread@stripped>
To: <dan@stripped>
Cc: <mysql@stripped>
Sent: Saturday, January 24, 2004 2:42 AM
Subject: Re: A fun one
>
> On 24-Jan-2004 dan@stripped wrote:
> > Here's a fun one to tickle your brains:
> >
> > I've got a table holding the results of a bandwidth tester thingie.
> > Its
> > columns hold the date and time the test was run, the fully-qualified
> > machine
> > name of the computer that took it, its IP address, and the speed in
> > kilobits/sec. What I'd like to do is compute the average for each
> > domain:
> > for example, there are many machine names in the adelphia.com domain,
> > and
> > I'd like to get an average of all adelphia.com results...and all
> > other
> > domains found in the database, which may increase daily.
> >
>
> See SUBSTRING_INDEX()
>
> Regards,
> --
> Don Read dread@stripped
> -- It's always darkest before the dawn. So if you are going to
> steal the neighbor's newspaper, that's the time to do it.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>
>
>
