opalepatrick

SELECT SQL_BIG_RESULT t1.ad_provider AS N, t1.adgroup, IF(SUBSTRING(t1.campaign, 1, 10) = 'Alpharooms', SUBSTRING_INDEX(t1.campaign,'_',3), t1.campaign) AS cam, SUM(t1.clicks) AS `clicks`, SUM(t1.sales_number) AS `salesno`, ROUND(SUM(t1.sale_value),2) AS `salestotal`, ROUND(SUM(t1.comm),2) AS `comm` , SUM(t1.ad_cost) AS `ad_cost`, ROUND(SUM(t1.comm)-SUM(t1.ad_cost), 2) AS `profit`, ROUND(SUM(t1.comm)/SUM(t1.sales_number), 2) AS `commpersale`, ROUND(SUM(t1.comm)/SUM(t1.clicks), 2) AS `commperclick`, ROUND(SUM(t1.cpc)/SUM(t1.clicks), 2) AS `cpc`, ROUND(SUM(t1.avgpos)/COUNT(*), 0) AS `avgpos`, ROUND((SUM(qs)/COUNT(*)), 1) AS `qs` 

FROM `clever`.`actual` AS t1 

WHERE (t1.trans_date >= '2009-01-01' AND t1.trans_date <= '11-05-04' || ( (t1.click_date >= '2009-01-01' AND t1.click_date <= '11-05-04') AND t1.comm = 0 )) AND t1.campaign LIKE 'Alpharooms_UK_D-I%' 

GROUP BY N, cam, t1.adgroup 

ORDER BY `profit`

| id | select_type | table | type  | possible_keys                  | key      | key_len | ref  | rows   | Extra                                        |
+----+-------------+-------+-------+--------------------------------+----------+---------+------+--------+----------------------------------------------+
|  1 | SIMPLE      | t1    | range | campaign,trans_date,click_date | campaign | 767     | NULL | 208308 | Using where; Using temporary; Using filesort |


CREATE TABLE `table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `click_date` date NOT NULL DEFAULT '0000-00-00',
  `trans_date` date NOT NULL DEFAULT '0000-00-00',
  `ad_provider` varchar(255) NOT NULL DEFAULT '',
  `keyword` varchar(255) NOT NULL DEFAULT '',
  `campaign` varchar(255) NOT NULL DEFAULT '',
  `adgroup` varchar(100) NOT NULL DEFAULT '',
  `creative` varchar(255) NOT NULL DEFAULT '',
  `sales_number` int(5) NOT NULL DEFAULT '0',
  `sale_value` decimal(10,2) NOT NULL DEFAULT '0.00',
  `comm` decimal(10,2) NOT NULL DEFAULT '0.00',
  `ad_cost` decimal(10,2) NOT NULL DEFAULT '0.00',
  `clicks` int(5) NOT NULL DEFAULT '0',
  `imp` int(5) NOT NULL DEFAULT '0',
  `cpc` decimal(5,2) DEFAULT NULL,
  `ctr` decimal(5,2) DEFAULT NULL,
  `avgpos` decimal(5,2) DEFAULT NULL,
  `qs` decimal(5,2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `campaign` (`campaign`),
  KEY `trans_date` (`trans_date`),
  KEY `adgroup` (`adgroup`),
  KEY `click_date` (`click_date`) USING BTREE,
  KEY `keyword` (`keyword`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=13206581 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC