Hi
I am writing a simple banner management script with three tables: banner, impression & clickthrough.
Banner has three columns: banner_id (pk), banner_name & banner_url
Impressions has 4 columns: impression_id (pk), banner_id (fk), banner_timestamp & banner_domain.
Clickthrough also has 4 columns: clickthrough_id (pk), banner_id (fk), clickthrough_timestamp & clickthrough_domain.
Every time a banner is shown, a record is created in the impression db and whenever one is clicked, the clickthrough db is similarly added to.
My problem comes when trying to use SQL to get the conversion rate for any particular banner. For each banner I need to count the total records in the impression db and then divide it by the count from the clickthrough db.
But I cannot for the life of me work out how to get counts from two tables and perform a mathematical operation on them.
Any help would be well appreciated,
Happy Christmas,
voidstate
