Hi Arcangelo!
Try this:
SELECT COUNT(*), url, SUBSTRING_INDEX(url, "?", 1) AS page_name
FROM sites
GROUP BY page_name
SUBSTRING_INDEX will return the leftmost (or rightmost) part of a string
before the ocurrence of a delimiter ("?" in this case. See Documentation:
Chapter "String Functions"). The trick here is to GROUP BY the result of
this operation.
Note that you could elaborate this further so that you could GROUP by
the host name. You will need to anidate SUBSTRING_INDEX calls so that you
end up with the desired data.
For example:
SELECT COUNT(*), url, SUBSTRING_INDEX(SUBSTRING_INDEX(url, "?", 1),
"/", 1) AS host_name
FROM sites
GROUP BY host_name
This will group together the following URIs as one:
- www.domain.com/pageone.php (There is no ?)
- www.domain.com?id=2 (There is no /)
Beware though, this usage will not use indexes on "url" column so be
careful and do not use this on big tables. You will need to use more
elaborated techniques to speed up query times on big tables.
Cheers,
Jose Miguel.
>----- ORIGINAL MESSAGE -----------
> I've a field with
>
> www.s.com/p.php?id=3
> www.s.com/p.php?id=4
> www.s.com/p.php?id=5
>
>
> if a do a query with the atribute 'group by' i want that the system
regroup
> all that is after the '?'
> is that possible?
>
> SELECT field WHERE all that is after the '?' GROUP BY all that is after
the
> '?'
> the result should be:
>
> www.s.com/p.php
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
