CODEWITHSUNDEEP
mysql
mauzilla
mauzilla

Reputation: 3592

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?

Upvotes: 0

Views: 316

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521987

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.

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

Upvotes: 2

Related Questions