Count returns duplicate of another count in mysql

Question

I can not find where is the problem, my COUNT() returns the same number for both values contracts_count and records_count. When using only one of counts, everything works fine. Principle of this query: access company table, get it's id and name, company and group is related with foreign key in contract table, and record has foreign key related to group table. So I join company with contracts to get contracts count of each company and join group with contracts, records with group to get records count of each company.

SELECT 
`company`.`id`,
`company`.`name`,
COUNT(`contract`.`contract_id`) AS contracts_count, 
COUNT(`record`.`record_id`) AS records_count
                    FROM `company`
                    LEFT JOIN `contract`
                            ON `company`.`id`=`contract`.`fk_companystudijos_id`
                    LEFT JOIN `group` 
                            ON `contract`.`fk_groupID`=`group`.`id`
                    LEFT JOIN `record` 
                            ON `group`.`id`=`record`.`fk_groupID`
                            GROUP BY `company`.`id`
                            ORDER BY contracts_count DESC

Gordon Linoff · Accepted Answer · 2020-05-20 19:02:02Z

1

COUNT() counts the number of rows with non-NULL values of its argument. That is pretty much the same for your two columns.

I'm pretty sure you want COUNT(DISTINCT):

COUNT(DISTINCT `contract`.`contract_id`) AS contracts_count, 
COUNT(DISTINCT `record`.`record_id`) AS records_count

answered May 20, 2020 at 19:02

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

Sophie Garcia Over a year ago

I have tried to use DISTINCT before, but I put it only on one COUNT(), thank you for the quick answer.

Collectives™ on Stack Overflow

Count returns duplicate of another count in mysql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related