Multiple COUNT() + JOIN = the same value?

There is a sample from a database, which is the JOIN of two different tables, these two tables is COUNT().

The fact that both of these count for some reason have absolutely the same value.

Example query:
SELECT
`t`.`id`,`t`.`title`,
 COUNT(`r`.`id`) AS `rcnt`,
 COUNT(`f`.`id`) AS `fcnt`
FROM
 `table` AS `t`
 LEFT JOIN
 `rtable` AS `r` ON `t`.`id` = `r`.`r_id`
 LEFT JOIN
 `ftable` AS `f` ON `f`.`w_id` = `t`.`w_id`
 GROUP BY
 `t`.`id`



(changed the field names, hence may be not the right query, but the point should be clear)

Then fcnt and rcnt are the same value for some reason.

Maybe someone knows why and how to avoid it?
October 8th 19 at 01:26
3 answers
October 8th 19 at 01:28
Solution
Try COUNT(DISTINCT r.id).
Thank you! It seems this is what you need :) - drake_Volkm commented on October 8th 19 at 01:31
wow!!! Will record his identity. - Rudolph.Dickinson commented on October 8th 19 at 01:34
This will give the number of unique values - Selmer_Roga commented on October 8th 19 at 01:37
I suspect that the id is unique, and that's what it takes man - aniya.Kertzmann65 commented on October 8th 19 at 01:40
Yes, of course id is unique and it is the number of unique rows and want to calculate. - drake_Volkm commented on October 8th 19 at 01:43
October 8th 19 at 01:30
Well, when you JOIN, rows, and here and there the same, then the COUNT works correctly.

Need to do 2 subquery separately to each table in this code fragment, COUNT(`r`.`id`) AS `rcnt`. Well, if I correctly understood the task.
Task — pigeonite 2 plates and see how many in each row. Here COUNT() is triggered in a strange way, returns znachenie the same for both tables, although in reality it is different.
With subqueries, Yes, of course, but I without them to do it. - drake_Volkm commented on October 8th 19 at 01:33
Going better now understandable task.
1) Select your query into a temporary table
2) Make 2 query SELECT DISTINCT COUNT(column) something like that. - Rudolph.Dickinson commented on October 8th 19 at 01:36
Yes that's right COUNT works — you think of the number of records in the current selection. - Selmer_Roga commented on October 8th 19 at 01:39
For clarity it is better then immediately break into two queries. - aniya.Kertzmann65 commented on October 8th 19 at 01:42
October 8th 19 at 01:32
The query you will only have N rows. Accordingly, each COUNT will return the number N, because COUNT considers NULL values.
You can change your request to:
SELECT `t`.`id`,`t`.`title`, (select COUNT(`id`) from rtable) AS `rcnt`, (select COUNT(`id`) from ftable) AS `fcnt`,
and blah blah blah
even I immediately realized what a silly thing wrote=)
(select COUNT(`id`) from rtable where id = t.id)
Here's a correlated query will be true - drake_Volkm commented on October 8th 19 at 01:35
What the hell? COUNT just did not take into account NULL. - Rudolph.Dickinson commented on October 8th 19 at 01:38

Find more questions by tags MySQL