* [EMAIL PROTECTED] > * Don Read > > See SUBSTRING_INDEX()
> 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; [...] You shouldn't use text columns for ip/name... ip addresses fits in an unsigned int, a varchar would be good for a host name. You should also consider making a separate domain column, if you need to do these queries on a regular basis and the amount of data is significant. You should also be carefull with the timestamp column type. If you for instance would try to add a domain column with ALTER TABLE ADD domain VARCHAR(255) NOT NULL or similar, and the do an UPDATE readings SET domain = SUBSTRING_INDEX(name,'.',-2), you will experience that the date column is changed as well, to the current time. This is a special feature of the timestamp column. You can avoid it by instead doing "UPDATE readings SET domain = SUBSTRING_INDEX(name,'.',-2),date=date", i.e. setting the date column to be equal to what it allready is... This is described in the manual: <URL: http://www.mysql.com/doc/en/DATETIME.html > If you want a DATETIME column instead of the TIMESTAMP: ALTER TABLE readings MODIFY date DATETIME NOT NULL; > 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! ;) I am trying to understand what the fun part is... :) There is only one "aol.com" record, the speed value for this record is 30.1, and you want the output to be "5.0 kbps average"? Was that just an example, or is that the fun part? You could divide by 6, but it does not match your other example values... I got 2184.5 as an average for adelphia.net _without_ dividing by 6. You want the output in the _form_ you specify above? Not columns, but one line for each domain, followed by a colon and then the average value? The inconsistent number of spaces between the colon and the number? Is that part of what you want? Is this the fun part? I guess not, but please be more specific. :) Try something like this: SELECT CONCAT( SUBSTRING_INDEX(name,'.',-2),': ', TRUNCATE(AVG(speed),1), ' kbps average') "Here's what I want:" FROM readings GROUP BY SUBSTRING_INDEX(name,'.',-2); or simply: SELECT SUBSTRING_INDEX(name,'.',-2) domain, TRUNCATE(AVG(speed),1) avgspeed FROM readings GROUP BY domain; -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
