I'm writing the banner stats system and now I have some
problems with a query which should show me the complete viewed
and clicked sums for each day.
Here's the table:
mysql> explain banner_access;
+-------------+--------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------------------+------+-----+---------+-------+
| banner_id | int(11) | YES | | NULL | |
| category_id | int(11) | YES | | NULL | |
| access_date | timestamp(14) | YES | | NULL | |
| access_type | enum('viewed','clicked') | YES | | viewed | |
+-------------+--------------------------+------+-----+---------+-------+
and
mysql> explain banner;
+-------------+-----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------+------+-----+---------+----------------+
| banner_id | int(11) | | PRI | 0 | auto_increment |
| image_file | char(64) | | | | |
| url | char(255) | | | | |
| banner_name | char(64) | | | | |
| added_by | int(11) | YES | | NULL | |
| active | int(1) | YES | | 1 | |
+-------------+-----------+------+-----+---------+----------------+
So, this is the query I tried:
$qry = "select
substring(access_date, 1, 8) as dan,
count(*) as c,
banner.banner_id as bid, banner_name, access_type
from banner_access, banner
where banner.banner_id = '$bid'
and
banner.banner_id=banner_access.banner_id
group by dan, access_type
order by dan, access_type";
and this is the sample output for some specific $bid:
+----------+-------------+----+-------------+
| dan | banner_name | c | access_type |
+----------+-------------+----+-------------+
| 20000124 | 43 | 19 | viewed |
| 20000124 | 43 | 1 | clicked |
| 20000125 | 43 | 11 | viewed |
| 20000125 | 43 | 1 | clicked |
| 20000128 | 43 | 18 | viewed |
| 20000202 | 43 | 2 | viewed |
| 20000202 | 43 | 1 | clicked |
| 20000207 | 43 | 8 | viewed |
| 20000209 | 43 | 13 | viewed |
| 20000210 | 43 | 36 | viewed |
| 20000211 | 43 | 16 | viewed |
+----------+-------------+----+-------------+
11 rows in set (0.00 sec)
It's obvious that there was no clicks on Jan.28th, Feb.07 etc.
What I'd like to have is the query which will inform me that c
column (count) is zero, like
| 20000128 | 43 | 18 | viewed |
| 20000128 | 43 | 0 | clicked |
After trying few different queries, I decided to post the
question here.
Any help?
Thanks.
--
v
Davor Cengija
davor@stripped
===================================
"Please allow 30 days for delivery"
