Grouping daily counts into Weekly hit counts

[rageh]

Hi everybody,

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

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

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.

[r937]

try this --

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

[rageh]

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

[r937]

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

[r937]

omg i just noticed you have VARCHAR(32) for your date column

that can't be good...

[rageh]

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.

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');

[r937]

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

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

[rageh]

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:

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.

[r937]

How can I refine the SQL query to show results by monthly?

EXTRACT(MONTH FROM visit_datetime) AS monthno

[rageh]

The result is exactly the same as before. This screenshot shows what I got when I used MONTH in place of WEEK.

[r937]

...what I got when I used MONTH in place of WEEK.

please show your GROUP BY clause for those results

[rageh]

please show your GROUP BY clause for those results

This is the whole SELECT statement.

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

[r937]

GROUP BY monthno DESC

there's your problem

it says monthno, but %V is week

[rageh]

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?

[r937]

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

[rageh]

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.