Grouping daily counts into Weekly hit counts

rageh · October 27, 2010, 12:02am

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 · October 27, 2010, 12:40am

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 · October 27, 2010, 1:41am

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 · October 27, 2010, 1:53am

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 · October 27, 2010, 1:55am

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

that can’t be good…

rageh · October 27, 2010, 2:50am

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; +http://yandex.com/bots)','index.php','http://www.codingpoint.co.uk/');



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 (http://www.linguee.com/bot; [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; +http://yandex.com/bots)','index.php','unknown');

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 (http://www.linguee.com/bot; [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; +http://yandex.com/bots)','index.php','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','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 · October 27, 2010, 10:47am

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 · October 27, 2010, 5:42pm

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 · October 27, 2010, 6:11pm

EXTRACT(MONTH FROM visit_datetime) AS monthno

rageh · October 27, 2010, 7:00pm

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

r937 · October 27, 2010, 10:55pm

please show your GROUP BY clause for those results

rageh · October 27, 2010, 11:12pm

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 · October 28, 2010, 2:12am

there’s your problem

it says monthno, but %V is week

rageh · October 28, 2010, 12:09pm

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 · October 28, 2010, 1:27pm

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 · October 28, 2010, 3:09pm

With MONTH function, it works like a charm. Thank you very much for your help.