SUM in SELECT with LEFT JOIN being multiplied together

Question

SUM in SELECT with LEFT JOIN being multiplied together

Navigation

#1 by (2 votes)
#2 by (0 votes)

4

I have 3 tables in MYSQL.

Account:

id  nome

1   caio
2   zé
3   marcelo

Followers (the account id, that is, the user has 2 followers):

idqual

1
1
2
2
2
3

Products (the account id, that is, the user caio has 3 products):

idqual
1
1
1
2
2
3

What I have to do is a query ordering by the number of products that each user has and by the number of followers that each user has, then I have to list how many products and how many followers each user has, I thought it would be easy to do this , and I did so:

SELECT conta.id, conta.nome, count(produtos.idqual) as somaprodutos, count(seguidores.idqual) as somaseguidores FROM conta 
LEFT JOIN produtos ON conta.id = produtos.idqual
LEFT JOIN seguidores ON conta.id = seguidores.idqual
GROUP BY conta.id, conta.nome
ORDER BY SUM(somaprodutos) DESC,
SUM(somaseguidores) DESC

The error that occurs is that the value of the sometors and the domaproducts is the same, and this value is always the multiplication of them, eg

He is saying that user Caio has 6 followers and 6 products (2 followers * 3 products), what did I do wrong?

mysql sql join select

asked by anonymous 21.09.2015 / 21:27

2 answers

0

I checked one and saw that many used the distinct

count(distinct produtos.idqual) as somaprodutos

I did some testing and solved my problem!

SELECT conta.id, conta.nome, count(distinct produtos.idqual) as somaprodutos, count(distinct seguidores.idqual) as somaseguidores FROM conta 
LEFT JOIN produtos ON conta.id = produtos.idqual
LEFT JOIN seguidores ON conta.id = seguidores.idqual
GROUP BY conta.id, conta.nome
ORDER BY SUM(somaprodutos) DESC,
SUM(somaseguidores) DESC

21.09.2015 / 22:15

How to encrypt images with algorithm RC5 in Java PHP - Array - Vertical Keys for Horizontal

score 2 · Accepted Answer

See if it helps:

        SELECT conta.id,
               conta.nome, 
               (select count(*) from produto where produto.idqual = conta.id) QtdProdutos,
               (select count(*) from seguidores where seguidores.idqual = conta.id) QtdSeguidores

        FROM conta

        GROUP BY  conta.id,
                  conta.nome

        ORDER BY QtdProdutos,QtdSeguidores