I am trying to return multiple count values within a single query. The query works but returns the same value for both of the count selectors:
$sql = 'SELECT '
. 'count(`ub`.`id`) `nummembers`,'
. 'count(`ca`.`id`) `numapps` '
. 'FROM '
. '`brands` `b` '
. 'LEFT JOIN `user_brands` `ub` ON `ub`.`brand_id`=`b`.`id` '
. 'LEFT JOIN `ca` ON `ca`.`brand_id`=`b`.`id` '
. 'GROUP BY `b`.`id`';
COUNT(DISTINCT col) if you want the number of unique members and apps within each brand group. The reason the counts were appearing the same in your original query is that you were counting the number of records in each group without regard to what is actually in each group. This will always give you same number regardless of which ID you choose to count.
SELECT b.*, COUNT(DISTINCT ub.id) nummembers, COUNT(DISTINCT ca.id) numapps, FROM brands b LEFT JOIN user_brands ub ON ub.brand_id = b.id LEFT JOIN ca ON ca.brand_id = b.id GROUP BY b.id