i have the following table in my application

CREATE TABLE `documentList` (
`id` INT UNSIGNED NOT NULL auto_increment,
`document` varchar(255) NOT NULL,
`archiveDate` date,
`contentType` varchar(30),
`resultCode` varchar(30),
`size` MEDIUMINT UNSIGNED ,
`title` varchar(200) ,
UNIQUE KEY `document_unique_key` (`document`),
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='list document urls..';

i want to create a list of unique host and how many documents it has in
the table from url
if i have following in the table

http://www.linux.org:80/
http://www.linux.org:80/xyz.html

i want to produce

host url count
http://www.linux.org | 2

i tried it with regex but i can only list unique host but cant count
them is there way to accoplish this?

nakkaya@gmail.com wrote:
> http://www.linux.org:80/
> http://www.linux.org:80/xyz.html
>
> i want to produce
>
> host url count
> http://www.linux.org | 2

SQL isn't the most convenient string-manipulation language, compared to
Perl and others, but there are ways to do it:

SELECT LEFT(document, LOCATE('/', document, 8)-1) AS `host`, COUNT(*) AS
`url count`
FROM documentList
GROUP BY host

Regards,
Bill K.

works like a charm thanks..

Bill Karwin wrote:
> nakkaya@gmail.com wrote:
> > http://www.linux.org:80/
> > http://www.linux.org:80/xyz.html
> >
> > i want to produce
> >
> > host url count
> > http://www.linux.org | 2
>
> SQL isn't the most convenient string-manipulation language, compared to
> Perl and others, but there are ways to do it:
>
> SELECT LEFT(document, LOCATE('/', document, 8)-1) AS `host`, COUNT(*) AS
> `url count`
> FROM documentList
> GROUP BY host
>
> Regards,
> Bill K.