Mauritz Swanepoel Mauritz Swanepoel - 1 year ago 48
SQL Question

MySQL returns same value for multiple count selects

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 '
. '`b`.*,'
. '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`';

I sense I am missing a condition but not sure if the above is possible within a single query?

Answer Source

Use 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.

       COUNT(DISTINCT nummembers,
       COUNT(DISTINCT numapps,
FROM brands b
LEFT JOIN user_brands ub
    ON ub.brand_id =
    ON ca.brand_id =