Re: Question about SQL statements......
- Original Message - From: David Souza [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, August 25, 2004 7:18 PM Subject: Question about SQL statements.. So I have all of this years apache access logs in a huge DB table, most of what I wanted to get out is working fine, but I am trying to get top 10 requests (hits) by day inside a certain section of the hierarchy, but I can't seem to get the correct results. I have been trying many different ways of specifying the SQL statement, so here is what I have so far: I have hits to a specific page, by week working fine here: mysql SELECT week(time_stamp, 1) as week, request_uri, count(*) as hits - FROM `access_log` - WHERE (request_uri=/testing/folder/foldersetup.exe) AND (time_stamp 2004040100 AND time_stamp 2004082300) - GROUP BY week - ORDER BY week ASC, hits DESC - LIMIT 20; +--+-+---+ | week | request_uri | hits | +--+-+---+ | 27 | /testing/folder/folderSetup.exe | 20069 | | 28 | /testing/folder/folderSetup.exe | 3785 | | 29 | /testing/folder/folderSetup.exe | 1607 | | 30 | /testing/folder/folderSetup.exe | 878 | | 31 | /testing/folder/folderSetup.exe | 858 | | 32 | /testing/folder/folderSetup.exe | 779 | | 33 | /testing/folder/folderSetup.exe | 721 | | 34 | /testing/folder/folderSetup.exe | 1015 | +--+-+---+ 8 rows in set (0.85 sec) but when I try to get top hits with a like statement by day (only 2 days) I get: mysql SELECT day(time_stamp) as day, request_uri, count(DISTINCT request_uri) as hits - FROM `access_log` - WHERE (time_stamp 2004050300 AND time_stamp 2004050500) AND (request_uri LIKE /docs/Carb/%) - GROUP BY day - ORDER BY day DESC, hits DESC - LIMIT 20; +--+---+--+ | day | request_uri | hits | +--+---+--+ |4 | /docs/Carb/index.html | 7075 | |3 | /docs/Carb/Conceptual/index.html | 6979 | +--+---+--+ 2 rows in set (39.68 sec) which seems to give me all the hits within /docs/Carb/ on the two days and then displays the last request_uri that came through. Maybe I cannot do this with one SQL statement or I just need more logic. Ideally what I would want would be something like: +- +--- +--+ | day | request_uri | hits | +- +--- +--+ | 4 | /docs/Carb/index.html | 1063 | | 4 | /docs/Carb/Conceptual/test_system/new_test_system.html | 930 | | 4 | /docs/Carb/Conceptual/test_system/toc.html | 921 | | 4 | /docs/Carb/Conceptual/test_system/ | 799 | | 4 | /docs/Carb/Conceptual/Testing_book/toc.html | 483 | | 4 | /docs/Carb/Conceptual/Testing_book/Intro/chapter_1_section_1.html | 479 | | 4 | /docs/Carb/Conceptual/Testing_book/index.html | 433 | | 4 | /docs/Carb/Conceptual/newQuickTour/qt_intro/chapter_1_section_1.html | 425 | | 4 | /docs/Carb/date.html | 421 | | 4 | /docs/Carb/Conceptual/newQuickTour/toc.html | 375 | | 3 | /docs/Carb/index.html | 1063 | | 3 | /docs/Carb/Conceptual/test_system/new_test_system.html | 930 | | 3 | /docs/Carb/Conceptual/test_system/toc.html | 921 | | 3 | /docs/Carb/Conceptual/test_system/ | 799 | | 3 | /docs/Carb/Conceptual/Testing_book/toc.html | 483 | | 3 | /docs/Carb/Conceptual/Testing_book/Intro/chapter_1_section_1.html | 479 | | 3 | /docs/Carb/Conceptual/Testing_book/index.html | 433 | | 3 | /docs/Carb/Conceptual/newQuickTour/chapter_1_section_1.html | 425 | | 3 | /docs/Carb/date.html | 421 | | 3 | /docs/Carb/Conceptual/newQuickTour/toc.html | 375 | +- +--- +--+ Any thoughts/help would be much appreciated!!! Change your GROUP BY to this: GROUP BY day, request_uri The rest of the query should be fine the
Re: Question about SQL statements......
You rock!! Worked perfectly On Aug 25, 2004, at 4:31 PM, Rhino wrote: - Original Message - From: David Souza [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, August 25, 2004 7:18 PM Subject: Question about SQL statements.. So I have all of this years apache access logs in a huge DB table, most of what I wanted to get out is working fine, but I am trying to get top 10 requests (hits) by day inside a certain section of the hierarchy, but I can't seem to get the correct results. I have been trying many different ways of specifying the SQL statement, so here is what I have so far: I have hits to a specific page, by week working fine here: mysql SELECT week(time_stamp, 1) as week, request_uri, count(*) as hits - FROM `access_log` - WHERE (request_uri=/testing/folder/foldersetup.exe) AND (time_stamp 2004040100 AND time_stamp 2004082300) - GROUP BY week - ORDER BY week ASC, hits DESC - LIMIT 20; +--+-+---+ | week | request_uri | hits | +--+-+---+ | 27 | /testing/folder/folderSetup.exe | 20069 | | 28 | /testing/folder/folderSetup.exe | 3785 | | 29 | /testing/folder/folderSetup.exe | 1607 | | 30 | /testing/folder/folderSetup.exe | 878 | | 31 | /testing/folder/folderSetup.exe | 858 | | 32 | /testing/folder/folderSetup.exe | 779 | | 33 | /testing/folder/folderSetup.exe | 721 | | 34 | /testing/folder/folderSetup.exe | 1015 | +--+-+---+ 8 rows in set (0.85 sec) but when I try to get top hits with a like statement by day (only 2 days) I get: mysql SELECT day(time_stamp) as day, request_uri, count(DISTINCT request_uri) as hits - FROM `access_log` - WHERE (time_stamp 2004050300 AND time_stamp 2004050500) AND (request_uri LIKE /docs/Carb/%) - GROUP BY day - ORDER BY day DESC, hits DESC - LIMIT 20; +--+---+--+ | day | request_uri | hits | +--+---+--+ |4 | /docs/Carb/index.html | 7075 | |3 | /docs/Carb/Conceptual/index.html | 6979 | +--+---+--+ 2 rows in set (39.68 sec) which seems to give me all the hits within /docs/Carb/ on the two days and then displays the last request_uri that came through. Maybe I cannot do this with one SQL statement or I just need more logic. Ideally what I would want would be something like: +- +- -- +--+ | day | request_uri | hits | +- +- -- +--+ | 4 | /docs/Carb/index.html | 1063 | | 4 | /docs/Carb/Conceptual/test_system/new_test_system.html | 930 | | 4 | /docs/Carb/Conceptual/test_system/toc.html | 921 | | 4 | /docs/Carb/Conceptual/test_system/ | 799 | | 4 | /docs/Carb/Conceptual/Testing_book/toc.html | 483 | | 4 | /docs/Carb/Conceptual/Testing_book/Intro/chapter_1_section_1.html | 479 | | 4 | /docs/Carb/Conceptual/Testing_book/index.html | 433 | | 4 | /docs/Carb/Conceptual/newQuickTour/qt_intro/chapter_1_section_1.html | 425 | | 4 | /docs/Carb/date.html | 421 | | 4 | /docs/Carb/Conceptual/newQuickTour/toc.html | 375 | | 3 | /docs/Carb/index.html | 1063 | | 3 | /docs/Carb/Conceptual/test_system/new_test_system.html | 930 | | 3 | /docs/Carb/Conceptual/test_system/toc.html | 921 | | 3 | /docs/Carb/Conceptual/test_system/ | 799 | | 3 | /docs/Carb/Conceptual/Testing_book/toc.html | 483 | | 3 | /docs/Carb/Conceptual/Testing_book/Intro/chapter_1_section_1.html | 479 | | 3 | /docs/Carb/Conceptual/Testing_book/index.html | 433 | | 3 | /docs/Carb/Conceptual/newQuickTour/chapter_1_section_1.html | 425 | | 3 | /docs/Carb/date.html | 421 | | 3 | /docs/Carb/Conceptual/newQuickTour/toc.html | 375 | +- +- -- +--+ Any thoughts/help would be much appreciated!!! Change your GROUP BY to this: GROUP BY day, request_uri The rest of the query should be fine the way it is. Rhino ** David Souza voice:408-974-1992 Web Specialist http://developer.apple.com/ Worldwide Developer Relations mailto:[EMAIL PROTECTED] **
RE: Question about IF statements...
From: Cory Hicks [mailto:[EMAIL PROTECTED] Hello, I must be having a goober moment.I am running the following sql query with no problems: SELECT project_id, IF (SUM( time_worked ) '0.00', SUM( time_worked ),'NULL') AS total FROM time_daily WHERE user_id = 'clh' AND period_id = '27' GROUP BY project_id However, having 'NULL' appear where the total is 0.00 doesn't do me much good :-) How do I write this query to only show me the results where the total is 0.00...so where the results that total 0.00 don't appear at all. I am sure I am missing something...thanks for any help! SELECT project_id, IF (SUM( time_worked ) '0.00', SUM( time_worked ),'NULL') AS total FROM time_daily WHERE user_id = 'clh' AND period_id = '27' AND total IS NOT NULL GROUP BY project_id; That should work, though I haven't tested it. -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about IF statements...
Would something like this do what you want? SELECT project_id, SUM(time_worked) AS total FROM time_daily WHERE user_id='clh' AND period_id='27' GROUP BY project_id HAVING total0; Cory Hicks wrote: Hello, I must be having a goober moment.I am running the following sql query with no problems: SELECT project_id, IF (SUM( time_worked ) '0.00', SUM( time_worked ),'NULL') AS total FROM time_daily WHERE user_id = 'clh' AND period_id = '27' GROUP BY project_id However, having 'NULL' appear where the total is 0.00 doesn't do me much good :-) How do I write this query to only show me the results where the total is 0.00...so where the results that total 0.00 don't appear at all. I am sure I am missing something...thanks for any help! Cory -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about IF statements...
Like this: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.0.13 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. umysql use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql create table time_daily (project_id int(3) primary key auto_increment, time_worked int(3), user_id varchar(3), period_id varchar(3)); Query OK, 0 rows affected (0.06 sec) mysql insert into time_daily (null, 3, 'clh', '27'); ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'null, 3, 'clh', '27')' at line 1 mysql insert into time_daily (time_worked, user_id, period_id) values (3, 'clh', '27'); Query OK, 1 row affected (0.52 sec) mysql insert into time_daily (time_worked, user_id, period_id) values (0, 'clh', '27'); Query OK, 1 row affected (0.00 sec) mysql insert into time_daily (time_worked, user_id, period_id) values (5, 'clh', '27'); Query OK, 1 row affected (0.00 sec) mysql SELECT project_id, SUM(time_worked) AS total FROM time_daily WHERE user_id='clh' AND period_id='27' GROUP BY project_id HAVING total0 - ; ++---+ | project_id | total | ++---+ | 1 | 3 | | 3 | 5 | ++---+ 2 rows in set (0.18 sec) Douglas Sims wrote: Would something like this do what you want? SELECT project_id, SUM(time_worked) AS total FROM time_daily WHERE user_id='clh' AND period_id='27' GROUP BY project_id HAVING total0; Cory Hicks wrote: Hello, I must be having a goober moment.I am running the following sql query with no problems: SELECT project_id, IF (SUM( time_worked ) '0.00', SUM( time_worked ),'NULL') AS total FROM time_daily WHERE user_id = 'clh' AND period_id = '27' GROUP BY project_id However, having 'NULL' appear where the total is 0.00 doesn't do me much good :-) How do I write this query to only show me the results where the total is 0.00...so where the results that total 0.00 don't appear at all. I am sure I am missing something...thanks for any help! Cory -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about IF statements...
That did the trickthanks so much! Cory On Wed, 2004-01-14 at 12:50, Douglas Sims wrote: Would something like this do what you want? SELECT project_id, SUM(time_worked) AS total FROM time_daily WHERE user_id='clh' AND period_id='27' GROUP BY project_id HAVING total0; Cory Hicks wrote: Hello, I must be having a goober moment.I am running the following sql query with no problems: SELECT project_id, IF (SUM( time_worked ) '0.00', SUM( time_worked ),'NULL') AS total FROM time_daily WHERE user_id = 'clh' AND period_id = '27' GROUP BY project_id However, having 'NULL' appear where the total is 0.00 doesn't do me much good :-) How do I write this query to only show me the results where the total is 0.00...so where the results that total 0.00 don't appear at all. I am sure I am missing something...thanks for any help! Cory -- Cory Hicks [EMAIL PROTECTED] TRI International -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about LIKE statements and escaped characters
At 07:55 AM 9/13/2001 -0500, Michael Sims wrote: Hi, I posted this query to the list via the newsgroup mailing.database.mysql a few days ago but didn't receive any replies. My apologies to the list...someone in alt.php was kind enough to point out that my question is answered in section 6.3.2.1 of the MySQL manual. Sorry for wasting time.. - 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
