Results 1 to 1 of 1
  1. #1
    Join Date
    Jan 2004
    Posts
    8

    Unanswered: Database Design / query construction

    Hello,
    Trying to build a afffiliate program so i made two tables.

    Clicks - with fields id, affiliate_id, ip, date, time

    and another tables
    Commisions with fields id, affiate_id, ip, date, time, amount

    now iam trying to output stats from a given start date to end date( for ex a week).

    so i made something like
    select date,count(ip) as raw, count(distinct ip) as uniq from clicks where affilate_id = '3444' and date >= '2004-02-25' and date <= '2004-03-02' group by date;

    it works fine for the clicks but i am faced with two problems
    1.) if a partner did not send any hits for a particular date that date is totally skipped in the results. so how would i fix it? using a programming language (PHP) or is there a another way?
    2.) i want to combine the total sales amounts for that date too from the commisions table. how would i join it ?

    in genereal i want a output like this

    ---------------------------------------------------
    |Date | Raws | Uniques | Amount |
    | 2004-02-25 | 50 | 15 | 20.00 |
    | 2004-02-26 | 0 | 0 | 0 |
    | 2004-02-27 | 100 | 75 | 0 |
    ..
    ...
    ...

    Thanx

    Any help would be appreciated.
    Last edited by manjitr; 03-03-04 at 01:21.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •