A Complex MySQL Query

I'm trying to write a complex SQL query but couldn't manage to get a result set with correct values. - `coupons` table holds some online coupons. - `merchants` table holds merchant info and tied to `coupons` with `coupons.merchant_id`. - `branches` table holds merchant's branches with `lat` and `lng` values (to calculate distance and nearest branches etc.) and tied to `merchants` table with `branches.merchant_id`. - `coupons_branches` table ties `coupons` and `branches` tables. - `places` table holds some special places, like shopping malls etc. and tied to `branches` table with `branches.place_id`. Below is my table structure; ``` CREATE TABLE IF NOT EXISTS `branches` ( `branch_id` int(11) unsigned NOT NULL AUTO_INCREMENT, `merchant_id` int(11) unsigned NOT NULL DEFAULT '0', `place_id` smallint(5) unsigned NOT NULL DEFAULT '0', `branch` varchar(40) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `address` varchar(255) COLLATE utf8_unicode_ci DEFAULT '', `postcode` varchar(6) COLLATE utf8_unicode_ci DEFAULT '', `phone` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL, `fax` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL, `lat` float(10,6) DEFAULT NULL, `lng` float(10,6) DEFAULT NULL, `status` tinyint(4) unsigned NOT NULL DEFAULT '1', PRIMARY KEY (`branch_id`), KEY `lat` (`lat`), KEY `lng` (`lng`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; CREATE TABLE IF NOT EXISTS `coupons` ( `coupon_id` mediumint(9) unsigned NOT NULL AUTO_INCREMENT, `category_id` tinyint(4) unsigned NOT NULL DEFAULT '0', `merchant_id` mediumint(9) unsigned NOT NULL DEFAULT '0', `coupon` varchar(200) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `description` longtext COLLATE utf8_unicode_ci, `start_date` date DEFAULT NULL, `end_date` date DEFAULT NULL, `coupon_usage` int(10) unsigned NOT NULL DEFAULT '0', `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `status` enum('active','passive','deleted','preview') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'active', PRIMARY KEY (`coupon_id`), KEY `start_date` (`start_date`), KEY `end_date` (`end_date`), KEY `merchant_id` (`merchant_id`), KEY `category_id` (`category_id`), KEY `status` (`status`), KEY `created` (`created`), FULLTEXT KEY `description` (`description`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; CREATE TABLE IF NOT EXISTS `coupons_branches` ( `branch_id` int(11) unsigned NOT NULL DEFAULT '0', `coupon_id` int(11) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`branch_id`,`coupon_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; CREATE TABLE IF NOT EXISTS `merchants` ( `merchant_id` int(11) unsigned NOT NULL AUTO_INCREMENT, `merchant` varchar(80) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `website` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL, `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `status` enum('active','passive','deleted') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'passive', PRIMARY KEY (`merchant_id`), KEY `status` (`status`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; CREATE TABLE IF NOT EXISTS `places` ( `place_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `city_id` smallint(5) unsigned NOT NULL DEFAULT '0', `place` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL, PRIMARY KEY (`place_id`), KEY `place` (`place`), KEY `city_id` (`city_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; ``` And here is my SQL; ``` SELECT * FROM (SELECT coupons.coupon_id AS c_id, coupons.category_id, coupons.coupon, merchants.merchant_id, merchants.merchant, (((acos(sin((41.02287686 * pi() / 180)) * sin((branches.lat * pi() / 180)) + cos((41.02287686 * pi() / 180)) * cos((branches.lat * pi()/ 180)) * cos(((29.04632806 - branches.lng) * pi() / 180)))) * 180 / pi()) * 60 * 1.1515 * 1.609344) as distance, COUNT(coupons.coupon_id) AS total_coupons FROM (`coupons`) INNER JOIN `coupons_branches` ON `coupons`.`coupon_id` = `coupons_branches`.`coupon_id` RIGHT OUTER JOIN `branches` ON `coupons_branches`.`branch_id` = `branches`.`branch_id` LEFT OUTER JOIN `merchants` ON `coupons`.`merchant_id` = `merchants`.`merchant_id` WHERE `coupons`.`status` = 'active' AND `merchants`.`status` = 'active' GROUP BY `merchants`.`merchant_id` HAVING `distance` <= 25000 ORDER BY `merchants`.`merchant`) as T2 GROUP BY c_id ORDER BY merchant ``` I'm trying to find out a merchant's total coupons. I want to list only the latest coupon from that merchant. I don't want to list all coupons. I want to group merchants first, show how many coupons that merchant have. Then at another page, I will display coupons belong to that merchant. Everything works great except `total_coupons` column. Total coupons column is not calculated correctly. If I write the like below, it calculates correctly. ``` SELECT * FROM (SELECT coupons.coupon_id, coupons.coupon_id AS c_id, coupons.category_id, coupons.coupon, merchants.merchant_id, merchants.merchant, coupons.coupon_usage, COUNT(coupons.coupon_id) AS total_coupons FROM (`coupons`) LEFT OUTER JOIN `merchants` ON `coupons`.`merchant_id` = `merchants`.`merchant_id` WHERE `coupons`.`status` = 'active' AND `merchants`.`status` = 'active' GROUP BY `merchants`.`merchant_id` ORDER BY `merchants`.`merchant`) as T2 GROUP BY c_id ORDER BY merchant ``` There is something wrong with `branches` and `places` tables but I couldn't find.