MySQL returns same value for multiple count selects

Question

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?

Add some sample data and your expected result.

Blank
– Blank

2016-07-15 07:38:45 +00:00
Commented Jul 15, 2016 at 7:38 — Blank
– Blank, Commented Jul 15, 2016 at 7:38

Tim Biegeleisen · Accepted Answer · 2016-07-15 07:41:25Z

2

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

answered Jul 15, 2016 at 7:41

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL returns same value for multiple count selects

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related