Need help constructing query ...
I have a table of full URLs and IPs and am using the following query to return
distinct web requests by domain. Using SUBSTRING_INDEX it only returns the
domain part of the URL:
SELECT SUBSTRING_INDEX(url, '/', 3) as topsites, count(distinct ip) as count
from tablename WHERE SUBSTRING_INDEX(url, '/', 3) LIKE '%mydomain%' group by
topsites order by count
Example output:
topsitescount
http://www.mydomain.com5
http://mydomain.com 3
My question is how do I modify the query to get it to merge requests for the
same domain by ignoring the "www." so that the above would return:
http://mydomain.com 8
I think it has something to do with adding
REPLACE('url', 'www.', '')
but I can't figure out where to put it to make it work.
Thanks!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help constructing query ...
: > -Original Message- : > From: John Kelly [mailto:[EMAIL PROTECTED] : > Sent: Tuesday, October 21, 2003 3:45 PM : > To: [EMAIL PROTECTED] : > Cc: [EMAIL PROTECTED] : > Subject: Re: Need help constructing query ... : > : > : > - Original Message - : > From: "Daniel Clark" <[EMAIL PROTECTED]> : > To: <[EMAIL PROTECTED]> : > Cc: <[EMAIL PROTECTED]> : > Sent: Tuesday, October 21, 2003 2:33 PM : > Subject: Re: Need help constructing query ... : > : > : > : > Hi, I have a table full of logged urls and ip addresses. : > The following : > : > query returns all the urls and the number of requests. How would I : > : > modify it to return unique requests based on distinct ip : > addresses? : > : > : > : > select url, count(*) as pageviews from table group by url order by : > : > pageviews desc : > : : > : How about: : > : : > : SELECT ip_address, url, count(*) : > : FROM tablename : > : GROUP BY ip_adress, url : > : : > Thanks but I could not get that to work. It does not appear : > to count the number of page requests by distinct IPs anyway : > does it? Don't you need something like a : > count(distinct(ip_address)) somewhere in there? : > : > -- - Original Message - From: "Kevin Fries" <[EMAIL PROTECTED]> To: "'John Kelly'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Tuesday, October 21, 2003 3:57 PM Subject: RE: Need help constructing query ... : Then I think you want : SELECT url, COUNT(DISTINCT ip_address) : FROM tablename : GROUP BY url; Thanks, this must be a resource intensive query as it works in a few seconds on a small table but takes 6+ minutes when done on a table with just 100,000 records. Anyway, thanks again. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help constructing query ...
- Original Message - From: "Daniel Clark" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Tuesday, October 21, 2003 2:33 PM Subject: Re: Need help constructing query ... : > Hi, I have a table full of logged urls and ip addresses. The following : > query returns all the urls and the number of requests. How would I : > modify it to return unique requests based on distinct ip addresses? : > : > select url, count(*) as pageviews from table group by url order by : > pageviews desc : : How about: : : SELECT ip_address, url, count(*) : FROM tablename : GROUP BY ip_adress, url : Thanks but I could not get that to work. It does not appear to count the number of page requests by distinct IPs anyway does it? Don't you need something like a count(distinct(ip_address)) somewhere in there? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need help constructing query ...
Hi, I have a table full of logged urls and ip addresses. The following query returns all the urls and the number of requests. How would I modify it to return unique requests based on distinct ip addresses? select url, count(*) as pageviews from table group by url order by pageviews desc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Create table if not exists from mysqldump?
Thanks for all the responses on how to import a dump file that will not write over existing tables and not stop with an error on existing tables - in other words just add missing tables. As Victoria pointed out, there is no option in mysqldump to add the appropriate create table option [IF NOT EXISTS] but one can add the --force option when importing the dump file back in with mysql. This causes mysql to ignore the error generated when it comes across a table that already exists and it will continue on adding any tables that are missing. Without it, mysql will stop on the first table that already exists with an error. An alternative solution is running a script on the dump file to replace all occurrences of " CREATE TABLE " with " CREATE TABLE IF NOT EXISTS " as suggested by Clayburn. Thanks again! John - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Create table if not exists from mysqldump?
Hi, I am trying to use mysqldump to dump the structure of a single database and want the output to make create table commands that include the IF NOT EXISTS switch so that when the file is imported into an existing database it does not write over tables with the same name. Does anyone know if this is possible with mysqldump and if so what option achieves this? Thanks. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Need help with a query ...
Hi, thanks for your response. I don't see how the LIKE command would help me pull records that matched the category name or its parent category if none exist, or its parent category if none exists. Perhaps you could provide a example. I want something that follows this logic ... SELECT * FROM table WHERE category = 'Sports:Football:Players' OR IF NONE category = 'Sports:Football' OR IF NONE category = 'Sports' Regarding the purpose of this query, it will be performed in every page/category in a web site directory looking to see if an advertisement is available for the current category, if not, it needs to look for one assigned to the parent category, so on and so on until it reaches the top level category for which there will always be an ad. Any suggestions/examples appreciated! John - Original Message - From: "Michael" <[EMAIL PROTECTED]> To: "John Kelly" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, November 02, 2001 7:09 PM Subject: Re: Need help with a query ... > You need to use the LIKE command I think. The MySQL manual should give you > all the details. If you need further help I can just write the whole query > out for you. Or was the problem that you only want to worry about the > if..then stuff if needed? If so could you tell what your pulling back from > that table? > > "The principal mark of genius is not perfection but originality, the > opening of new frontiers." >-- Arthur Koestler > > *^*^*^* > Michael McGlothlin <[EMAIL PROTECTED]> > http://mlug.missouri.edu/~mogmios/projects/ > > On Fri, 2 Nov 2001, John Kelly wrote: > > > Hi, I have a MySQL table with a column that contains some of a web site > > directory's category names in the format: > > > > Sports:Football:Players > > > > I am trying to build a query that that locates all records that match the > > above category name OR if none exist its parent "Sports:Football" OR if none > > exist its parent "Sports". The top level category, in this case "Sports", > > will always have at least one matching record. > > > > I know I can do this with multiple queries by checking the previous query's > > result, but I am trying to build a query that does it all in one lookup to > > avoid lots of lookups in deep categories. Something along the logical lines > > of ... > > > > SELECT * FROM table WHERE category = 'Sports:Football:Players' OR IF NONE > > category = 'Sports:Football' OR IF NONE category = 'Sports' > > > > ... of course the above query does not work but if anyone knows of how to > > accomplish something similar in one query I would much appreciate it. > > > > Thanks! > > > > > > > > - > > Before posting, please check: > >http://www.mysql.com/manual.php (the manual) > >http://lists.mysql.com/ (the list archive) > > > > To request this thread, e-mail <[EMAIL PROTECTED]> > > To unsubscribe, e-mail <[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Need help with a query ...
Hi, I have a MySQL table with a column that contains some of a web site directory's category names in the format: Sports:Football:Players I am trying to build a query that that locates all records that match the above category name OR if none exist its parent "Sports:Football" OR if none exist its parent "Sports". The top level category, in this case "Sports", will always have at least one matching record. I know I can do this with multiple queries by checking the previous query's result, but I am trying to build a query that does it all in one lookup to avoid lots of lookups in deep categories. Something along the logical lines of ... SELECT * FROM table WHERE category = 'Sports:Football:Players' OR IF NONE category = 'Sports:Football' OR IF NONE category = 'Sports' ... of course the above query does not work but if anyone knows of how to accomplish something similar in one query I would much appreciate it. Thanks! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
