0
I have the following query that should bring the sum of views and last view (table sir_ter_relatorios_terrenos
), sum of favourites (table sir_ter_favoritos
) and the person’s name (table sir_users
)
SELECT u.id as idinc, u.name as nome, sum(r.views) as totalviews, max(r.data) as ultimav, COUNT(f.status)
FROM sir_ter_relatorios_terrenos r, sir_users u
LEFT JOIN sir_ter_favoritos f ON f.idincorporadora = u.id
WHERE u.id = r.iduser
GROUP BY u.id
But when it’s time to bring the COUNT(f.status)
is going wrong, instead of bringing 1 (which is the only record you have in this table) it is bringing 3, which is an item count in the sir_ter_relatorios_terrenos
.
Example:
sir_ter_relatorios_terrenos
id|iduser|data |views
1 | 771 |2018-05-29| 3
2 | 771 |2018-05-29| 1
3 | 778 |2018-05-29| 1
sir_ter_favoritos
id|idincorporadora|status
1 | 771 | 1
sir_users
id |name
771 | João
778 | Maria
Result that should come:
idinc|nome |totalviews|ultimav |COUNT(f.status)
771 |João | 4 |2018-05-29| 1
778 |Maria| 1 |2018-05-29| 0
Outcome that is coming:
idinc|nome |totalviews|ultimav |COUNT(f.status)
771 |João | 4 |2018-05-29| 2
778 |Maria| 1 |2018-05-29| 0
Can’t you set an example with some data to facilitate understanding? If you are duplicating the data, you may need to group, but without seeing the data it is difficult
– Ricardo Pontual
opa, of course, I edited the answer, I put an example
– Leandro Marzullo
Now :) apparently is doubling because of the table
sir_ter_relatorios_terrenos
have two records with the sameiduser
(771). Including this tablesir_users
could be in theJOIN
and not in theWHERE
, would be clearer the relation. I found it strange not to error in the query because in select has the fieldu.name as nome
that is not stapled and also not part of thegroup by
, this should generate an error in this field– Ricardo Pontual
understood, then, it should bring Count as 1, because it has 1 entry in the favorites, no?
– Leandro Marzullo
I managed to find the solution, as put here?
– Leandro Marzullo
Just write in the answer and post explained, you can answer the question itself
– Ricardo Pontual