List:General Discussion« Previous MessageNext Message »
From:Steve Edberg Date:February 16 2000 8:08pm
Subject:Re: showing unexistat rows as zero
View as plain text  
At 3:13 PM +0100 2/16/00, Davor Cengija wrote:
>	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
>

<SNIP>

>+----------+-------------+----+-------------+
>| 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.
>--

I don't think you can do that in a query the way your database is set 
up. That would require MySQL to know that your table 'should' contain 
one record for every day. I see two options:

(1) As you iterate through the result's rows in whatever programming 
language you use, check for missing dates; or,

(2) Preload the table with ALL dates you want, with their values 
initialized to 0. The, instead of using an INSERT query in your 
banner stats system, use an UPDATE or REPLACE at all times. You could 
probably write a little program to go back in to your current tables 
an add the missing dates.

If I were me, I'd do (2)

- steve

+---------------- Splurk! Glort! Klikrunk! Ploip! Katoong!---------------+
| Steve Edberg                           University of California, Davis |
| sbedberg@stripped                                     (530)754-9127 |
| Computer Consultant                         http://aesric.ucdavis.edu/ |
+------------------ Don Martin 18 May 1931 - 7 Jan 2000 -----------------+
Thread
showing unexistat rows as zeroDavor Cengija16 Feb
  • Re: showing unexistat rows as zeroSteve Edberg16 Feb
    • Re: showing unexistat rows as zeroDavor Cengija17 Feb