SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Guru rageh's Avatar
    Join Date
    Apr 2006
    Location
    London, Formerly Somalia
    Posts
    612
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Grouping daily counts into Weekly hit counts

    Hi everybody,

    I have SQL query that returns date and the number of hits the website received for that date. The table is

    Code MySQL:
    CREATE TABLE vtble (
     	country_id int unsigned NOT NULL auto_increment,
        visit_time varchar(32) NOT NULL default '',
    	visit_date varchar(32) NOT NULL default '',
    	user_ip varchar(32) NOT NULL default '',
    	country varchar(64) NOT NULL default '',
    	user_agent varchar(128) NOT NULL default '',
    	page varchar(64) NOT NULL default '',
    	referrer varchar(128) NOT NULL default '',
      PRIMARY KEY  (country_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    And the the query is

    Code MySQL:
    SELECT visit_date, COUNT( * ) AS count
    			FROM vtble GROUP BY visit_date
    			ORDER BY visit_date DESC
    For example, 22-10-2010 and the corresponding hit count is 5. Again date 24-10-2010 and the corresponding count is 8 and so on.

    What I want instead is weeks shown in this way 22-10-2010 till 29-10-2010 and the corresponding hits for that 37 days. That is just an example.

    I am not sure if this question belongs to the database forum. I would be grateful if someone can help.
    ------------------

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    try this --
    Code:
    SELECT EXTRACT(WEEK FROM visit_date) AS weekno
         , MIN(visit_date) AS week_start
         , MAX(visit_date) AS week_end
         , COUNT(*) AS count
      FROM vtble 
    GROUP 
        BY weekno DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru rageh's Avatar
    Join Date
    Apr 2006
    Location
    London, Formerly Somalia
    Posts
    612
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you Rudy.

    Your SQL shows only one week. For example, the count started on 04/10/2010, and the last hit was 27/10/2010. But the query shows 04/10/2010(week_start) - 09/10/2010(week_end) and all the hits all the way up the 27/10/2010. And it does not show the rest of the dates. The hit counts shown are all of the hits from 04/10/2010 to 27/10/2010 instead of the first week's hits for that week.

    Also, from 4th to 9th is 6 days, not 7 days. By the way, the weekno returns NULL.

    It should show it like

    04/10/2010 - 10/10/2010 ---- number of hits
    11/10/2010 - 17/10/2010 -----number of hits
    18/10/2010 - 24/10/2010 -----number of hits

    and so on. I hope that makes it clear. Thanks again
    ------------------

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you know how you gave us the CREATE TABLE statement in post #1?

    please also give us a few INSERT statements with sample data

    i need to test the query, because i thought it was just what you wanted
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    omg i just noticed you have VARCHAR(32) for your date column

    that can't be good...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Guru rageh's Avatar
    Join Date
    Apr 2006
    Location
    London, Formerly Somalia
    Posts
    612
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes I did use VARCHAR(32) for both date and time. I know I shouldn't. But for the purpose of this query, please feel free to change.

    And how would I structure the SQL query if I wanted monthly tally. Like each month and the hits for that month? Is that simpler or harder?


    Here are few insert statements so that you can populate the table.


    Code MySQL:
    INSERT INTO vtble VALUES(NULL, '04-10-2010', '00:03:57', '94.23.211.138', 'United Kingdon', 'Opera/9.80 (Windows NT 6.0; U; en) Presto/2.6.30 Version/10.63','index.php','http://www.codingpoint.co.uk/portfolio.php');
     
    INSERT INTO vtble VALUES(NULL, '04-10-2010', '00:04:57', '94.23.211.138', 'France', 'Opera/9.80 (Windows NT 6.0; U; en) Presto/2.6.30 Version/10.63','index.php','http://www.codingpoint.co.uk/contact.php');
     
    INSERT INTO vtble VALUES(NULL, '08-10-2010', '10:03:17', '94.23.211.138', 'United Kingdon', 'Mozilla/5.0 (compatible; YandexBot/3.0; MirrorDetector; +[url]http://yandex.com/bots)','index.php','http://www.codingpoint.co.uk/');[/url]
     
     
     
    INSERT INTO vtble VALUES(NULL, '08-10-2010', '08:11:00', '207.46.199.30', 'United States', 'Opera/9.80 (Windows NT 6.0; U; en) Presto/2.6.30 Version/10.63','index.php','unknown');
     
     
    INSERT INTO vtble VALUES(NULL, '10-10-2010', '08:11:00', '207.46.199.30', 'United States', 'Opera/9.80 (Windows NT 6.0; U; en) Presto/2.6.30 Version/10.63','index.php','unknown');
     
     
    INSERT INTO vtble VALUES(NULL, '10-10-2010', '08:11:00', '207.46.199.30', 'United States', 'Opera/9.80 (Windows NT 6.0; U; en) Presto/2.6.30 Version/10.63','index.php','unknown');
     
     
    INSERT INTO vtble VALUES(NULL, '12-10-2010', '08:11:00', '207.46.199.30', 'United States', 'Opera/9.80 (Windows NT 6.0; U; en) Presto/2.6.30 Version/10.63','index.php','unknown');
     
     
    INSERT INTO vtble VALUES(NULL, '12-10-2010', '08:11:00', '207.46.199.30', 'United States', 'Opera/9.80 (Windows NT 6.0; U; en) Presto/2.6.30 Version/10.63','index.php','unknown');
     
     
    INSERT INTO vtble VALUES(NULL, '13-10-2010', '12:04:50', '90.23.211.10', 'United Kingdon', 'page_test [email]larbin2.6.3@unspecified.mail','index.php[/email]','unknown');
     
    INSERT INTO vtble VALUES(NULL, '15-10-2010', '12:04:50', '95.108.150.235', 'Russian Federation', 'Opera/9.80 (Windows NT 6.0; U; en) Presto/2.6.30 Version/10.63','index.php','unknown');
     
    INSERT INTO vtble VALUES(NULL, '17-10-2010', '19:01:00', '85.25.124.132', 'Germany', 'Linguee Bot ([url]http://www.linguee.com/bot;[/url] [email]bot@linguee.com)','index.php[/email]','unknown');
     
     
     
    INSERT INTO vtble VALUES(NULL, '17-10-2010', '08:11:00', '207.46.199.30', 'United States', 'Opera/9.80 (Windows NT 6.0; U; en) Presto/2.6.30 Version/10.63','index.php','unknown');
     
     
     
    INSERT INTO vtble VALUES(NULL, '15-10-2010', '08:11:00', '207.46.199.30', 'United States', 'Opera/9.80 (Windows NT 6.0; U; en) Presto/2.6.30 Version/10.63','index.php','unknown');
     
     
    INSERT INTO vtble VALUES(NULL, '18-10-2010', '11:01:00', '85.25.124.132', 'Germany', 'Mozilla/5.0 (compatible; YandexBot/3.0; MirrorDetector; +[url]http://yandex.com/bots)','index.php','unknown');[/url]
     
    INSERT INTO vtble VALUES(NULL, '19-10-2010', '01:08:00', '207.25.124.130', 'Canada', 'Opera/9.80 (Windows NT 6.0; U; en) Presto/2.6.30 Version/10.63','index.php','unknown');
     
    INSERT INTO vtble VALUES(NULL, '24-10-2010', '12:18:00', '85.25.124.132', 'Germany', 'Linguee Bot ([url]http://www.linguee.com/bot;[/url] [email]bot@linguee.com)','index.php[/email]','unknown');
     
    INSERT INTO vtble VALUES(NULL, '26-10-2010', '08:11:00', '207.46.199.30', 'United States', 'Opera/9.80 (Windows NT 6.0; U; en) Presto/2.6.30 Version/10.63','index.php','unknown');
     
    INSERT INTO vtble VALUES(NULL, '27-10-2010', '11:01:00', '207.46.199.37', 'United States', 'Mozilla/5.0 (compatible; YandexBot/3.0; MirrorDetector; +[url]http://yandex.com/bots)','index.php','unknown');[/url]
     
     
     
    INSERT INTO vtble VALUES(NULL, '26-10-2010', '08:11:00', '207.46.199.30', 'United States', 'Opera/9.80 (Windows NT 6.0; U; en) Presto/2.6.30 Version/10.63','index.php','www.spdu.info');
     
     
    INSERT INTO vtble VALUES(NULL, '27-10-2010', '09:12:10', '207.46.166.130', 'United States', 'Opera/9.80 (Windows NT 6.0; U; en) Presto/2.6.30 Version/10.63','index.php','www.spdu.info');
    ------------------

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i created a table using a single DATETIME column instead of your two VARCHARs for date and time

    i don't understand why, but my EXTRACT function doesn't seem to work with WEEK

    works okay with MONTH and YEAR, but not WEEK

    so i switched to DATE_FORMAT(visit_datetime,'%V') AS weekno and that worked
    Code:
    weekno week_start           week_end              count
    43     2010-10-24 12:18:00  2010-10-27 11:01:00     5
    42     2010-10-17 08:11:00  2010-10-19 01:08:00     4
    41     2010-10-10 08:11:00  2010-10-15 12:04:50     7
    40     2010-10-04 00:03:57  2010-10-08 10:03:17     4
    i did not attempt to make the query work with your VARCHARs

    i suggest you redefine your table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Guru rageh's Avatar
    Join Date
    Apr 2006
    Location
    London, Formerly Somalia
    Posts
    612
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The weekly dates are not working out properly. I have changed the two visit_date and visits_time varchars into one datetime column. And I used your query.

    This is the Select statement:

    Code MySQL:
    SELECT EXTRACT(WEEK FROM visit_datetime)
    	 , DATE_FORMAT(visit_datetime,'%V') AS weekno
                , MIN(visit_datetime) AS week_start
                , MAX(visit_datetime) AS week_end
                , COUNT(*) AS count
           FROM vtble 
    GROUP 
        BY weekno DESC


    The result as screenshot




    You said it is easier to do it by monthly. How can I refine the SQL query to show results by monthly?

    Thank you again.
    ------------------

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by rageh View Post
    How can I refine the SQL query to show results by monthly?
    EXTRACT(MONTH FROM visit_datetime) AS monthno

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Guru rageh's Avatar
    Join Date
    Apr 2006
    Location
    London, Formerly Somalia
    Posts
    612
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The result is exactly the same as before. This screenshot shows what I got when I used MONTH in place of WEEK.

    ------------------

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by rageh View Post
    ...what I got when I used MONTH in place of WEEK.
    please show your GROUP BY clause for those results
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Guru rageh's Avatar
    Join Date
    Apr 2006
    Location
    London, Formerly Somalia
    Posts
    612
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    please show your GROUP BY clause for those results
    This is the whole SELECT statement.

    Code MySQL:
    SELECT EXTRACT(MONTH FROM visit_datetime)
    	 , DATE_FORMAT(visit_datetime,'%V') AS monthno
         , MIN(visit_datetime) AS month_start
         , MAX(visit_datetime) AS month_end
         , COUNT(*) AS count
      FROM vtble 
    GROUP 
        BY monthno DESC
    ------------------

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by rageh View Post
    GROUP BY monthno DESC
    there's your problem

    it says monthno, but %V is week
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Guru rageh's Avatar
    Join Date
    Apr 2006
    Location
    London, Formerly Somalia
    Posts
    612
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    there's your problem

    it says monthno, but %V is week
    I completely overlooked that. Now solved. Thank you.

    One last thing is that the months are not in order. Without the DESC they come like 10, 6, 7, 8, 9 and with the DESC they come 9, 8 7, 6, 10. How can I order the monthno?
    ------------------

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by rageh View Post
    How can I order the monthno?
    DATE_FORMAT produces strings, and i wouldn't use that

    for some reason i couldn't get EXTRACT to work withWEEK, but it works just fine with MONTH

    you could also use the MONTH function itself
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Guru rageh's Avatar
    Join Date
    Apr 2006
    Location
    London, Formerly Somalia
    Posts
    612
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    DATE_FORMAT produces strings, and i wouldn't use that

    for some reason i couldn't get EXTRACT to work withWEEK, but it works just fine with MONTH

    you could also use the MONTH function itself
    With MONTH function, it works like a charm. Thank you very much for your help.
    ------------------


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •