SUM in SELECT with LEFT JOIN being multiplied

Asked

Viewed 1,405 times

4

I have 3 tables in MYSQL.

Bill:

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 sorting 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 somafollowers and the domaproducts is the same, and this value is always the multiplication of them, example:

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

mysql sql select join

  • Explain to me the meaning of this division: SUM(produtos.idqual/produtos.idqual)?

  • So, I had to add up how many times you go through each product, then I took the user id (idqual) and divided by himself, to always give 1, and then add up 1 + 1 + 1, to give the number of products that the user has. It was a certain gambit....

  • You can use the function count() for that reason. =)

  • Opa, thanks, I did not know, but he continues multiplying the somaprodutos with the somafollowers....

2 answers

2


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

  • So it’s always 0, it finds nothing...

  • Opa IAGO, thanks for the help, veja la que editei a minha pergunta, I put the correct form now Count and continue multiplying the values, strange right?

  • you need to group by name too

  • Still, they multiply... it only works for those who have no followers or those who have no products... for example, if a user has 3 products and 0 followers Qtdproducts of 3 and Qtdseguidors 0.... But if the user has followers and products, they multiply...

  • Oops, missed the distinct, thanks for the help!!

  • Caio, look at Edit

  • 1

    Oops, so was also straight, thanks!

Show 2 more comments

1

I did some research and saw that many were using distinct 

count(distinct produtos.idqual) as somaprodutos

I did some tests 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

  • You saved me a whole day’s work, thank you very much!

Browser other questions tagged mysql sql select join

You are not signed in. Login or sign up in order to post.