Iphone

Question: Hi, I have a hit counter for my website which is inserting data on the hits into my 'hit_counter' table in a MySQL database. I want to query the database to get a count of how many hits there have been per week, and a formatted string of the date for either the first of last day of that week. Is this possible in a single query, and if so what would the code be?

Iphone

C:\>mysql -uroot -p test
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 25 to server version: 5.0.18-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table hits(
-> ct int,
-> dt date);
Query OK, 0 rows affected (0.36 sec)

mysql> insert into hits values (1,'2005-02-02');
Query OK, 1 row affected (0.18 sec)

mysql> insert into hits values (1,'2005-12-02');
Query OK, 1 row affected (0.05 sec)

mysql> insert into hits values (4,'2005-12-02');
Query OK, 1 row affected (0.06 sec)

mysql> insert into hits values (4,'2005-01-02');
Query OK, 1 row affected (0.05 sec)

mysql> insert into hits values (4,'2003-01-02');
Query OK, 1 row affected (0.07 sec)

mysql> insert into hits values (4,'2003-10-02');
Query OK, 1 row affected (0.05 sec)

mysql> insert into hits values (4,'2003-10-12');
Query OK, 1 row affected (0.04 sec)

mysql> insert into hits values (4,'2003-10-13');
Query OK, 1 row affected (0.06 sec)

mysql> insert into hits values (14,'2003-10-13');
Query OK, 1 row affected (0.03 sec)

mysql> insert into hits values (14,'2005-10-13');
Query OK, 1 row affected (0.05 sec)

mysql> insert into hits values (14,'2005-11-13');
Query OK, 1 row affected (0.04 sec)

mysql> insert into hits values (14,'2005-11-15');
Query OK, 1 row affected (0.03 sec)

mysql> insert into hits values (140,'2005-11-16');
Query OK, 1 row affected (0.04 sec)

mysql> insert into hits values (140,'2005-10-16');
Query OK, 1 row affected (0.03 sec)

mysql> insert into hits values (1990,'2005-10-16');
Query OK, 1 row affected (0.04 sec)

mysql> select * from hits;
+------+------------+
| ct | dt |
+------+------------+
| 1 | 2005-02-02 |
| 1 | 2005-12-02 |
| 4 | 2005-12-02 |
| 4 | 2005-01-02 |
| 4 | 2003-01-02 |
| 4 | 2003-10-02 |
| 4 | 2003-10-12 |
| 4 | 2003-10-13 |
| 14 | 2003-10-13 |
| 14 | 2005-10-13 |
| 14 | 2005-11-13 |
| 14 | 2005-11-15 |
| 140 | 2005-11-16 |
| 140 | 2005-10-16 |
| 1990 | 2005-10-16 |
+------+------------+
15 rows in set (0.00 sec)

mysql> Select concat(extract(week from dt), ' week of ',extract(year from dt)) as weeki, sum(ct) FROm hits group by weeki;
+-----------------+---------+
| weeki | sum(ct) |
+-----------------+---------+
| 0 week of 2003 | 4 |
| 1 week of 2005 | 4 |
| 39 week of 2003 | 4 |
| 41 week of 2003 | 22 |
| 41 week of 2005 | 14 |
| 42 week of 2005 | 2130 |
| 46 week of 2005 | 168 |
| 48 week of 2005 | 5 |
| 5 week of 2005 | 1 |
+-----------------+---------+
9 rows in set (0.02 sec)

>>>"An alternative way to display"

mysql> Select concat('Week ',extract(week from dt)+1, ' of ',extract(year from dt)) as theWeek, sum(ct) as HITS FROM hits group by theWeek;
+-----------------+------+
| theWeek | HITS |
+-----------------+------+
| Week 1 of 2003 | 4 |
| Week 2 of 2005 | 4 |
| Week 40 of 2003 | 4 |
| Week 42 of 2003 | 22 |
| Week 42 of 2005 | 14 |
| Week 43 of 2005 | 2130 |
| Week 47 of 2005 | 168 |
| Week 49 of 2005 | 5 |
| Week 6 of 2005 | 1 |
+-----------------+------+
9 rows in set (0.00 sec)

Let me know if you need more info....