SELECT DATE_FORMAT(clicks.affiliate_clientdate, '%M %D, %Y') AS date, COUNT(affiliate_clickthrough_id) AS hits, count(affiliate_orders_id) AS sells FROM affiliate_clickthroughs AS clicks LEFT JOIN affiliate_stores_sales as sales USING(affiliate_id) WHERE clicks.affiliate_id = '111' AND clicks.affiliate_stores_id = '123' AND MONTH(clicks.affiliate_clientdate) = '06' AND YEAR(clicks.affiliate_clientdate) = '2003' GROUP BY date;
Does that do what you are looking for? Regards, Mike Hillyer www.vbmysql.com -----Original Message----- From: Ralph [mailto:[EMAIL PROTECTED] Sent: Thursday, June 05, 2003 10:33 AM To: Mike Hillyer; [EMAIL PROTECTED] Subject: RE: Count on Multiple Tables CREATE TABLE `affiliate_clickthroughs` ( `affiliate_clickthrough_id` int(11) NOT NULL auto_increment, `affiliate_id` int(11) NOT NULL default '0', `affiliate_stores_id` bigint(11) NOT NULL default '0', `affiliate_clientdate` datetime NOT NULL default '0000-00-00 00:00:00', `affiliate_clientbrowser` varchar(200) default 'Could Not Find This Data', `affiliate_clientip` varchar(50) default 'Could Not Find This Data', `affiliate_clientreferer` varchar(200) default 'none detected (maybe a direct link)', `affiliate_products_id` int(11) default '0', `affiliate_banner_id` int(11) NOT NULL default '0', PRIMARY KEY (`affiliate_clickthrough_id`), KEY `refid` (`affiliate_id`) ) TYPE=MyISAM AUTO_INCREMENT=9 ; CREATE TABLE `affiliate_stores_sales` ( `affiliate_id` int(11) NOT NULL default '0', `affiliate_stores_id` bigint(11) NOT NULL default '0', `affiliate_orders_id` int(11) NOT NULL default '0', `affiliate_clickthroughs_id` int(11) NOT NULL default '0', `affiliate_payment_id` int(11) NOT NULL default '0', `date` datetime NOT NULL default '0000-00-00 00:00:00', `browser` varchar(100) NOT NULL default '', `ipaddress` varchar(20) NOT NULL default '', `sale` decimal(15,2) NOT NULL default '0.00', `commission` decimal(15,2) NOT NULL default '0.00', `billing_status` int(5) NOT NULL default '0', `payment_date` datetime NOT NULL default '0000-00-00 00:00:00', `commission_percent` decimal(4,2) NOT NULL default '0.00', PRIMARY KEY (`affiliate_orders_id`) ) TYPE=MyISAM; -----Original Message----- From: Mike Hillyer [mailto:[EMAIL PROTECTED] Sent: Thursday, June 05, 2003 7:09 AM To: Ralph; [EMAIL PROTECTED] Subject: RE: Count on Multiple Tables Can you show some table structure so we have something work with? It's hard to recommend a query when we do not know what your sales table structure is. Regards, Mike Hillyer www.vbmysql.com -----Original Message----- From: Ralph [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 04, 2003 4:00 PM To: [EMAIL PROTECTED] Subject: Count on Multiple Tables I've been stuck on this one all morning. Can't seem to figure it out. I have 2 tables, one with affiliate sales and another with affiliate clickthroughs. I have to query both tables, so that I can get clickthrough dates, hits, and then query affiliate sales table to get number of orders for each date. I want to display the results like this: DATE | TOTAL HITS | TOTAL SALES 05/03/2003 6 1 05/04/2003 7 0 I've managed to get dates and total hits by using the following query: SELECT DATE_FORMAT(affiliate_clientdate, '%M %D, %Y') AS date, COUNT(*) AS hits FROM affiliate_clickthroughs WHERE affiliate_id = '111' AND affiliate_stores_id = '123' AND MONTH(affiliate_clientdate) = '06' AND YEAR(affiliate_clientdate) = '2003' GROUP BY date"; But then I can't get the total number of sales on affiliate sales table. Any suggestions? Your help is greatly appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
