Left Join with Two Counts from the same table

Question

Good people, I have a system that I made to make a pool with my friends. I want to select users and then count the right guesses and the wrong guesses. The database looks like this (I removed the useless parts to facilitate):

Table users: iduser, name

Table hunches: game, game, game, game, game, game, game

There’s another table with the games, but it’s irrelevant here.

I did the following query:

SELECT u.iduser, COUNT(p1.idpalpite) acertos, COUNT(p2.idpalpite) erros
FROM usuarios u
LEFT JOIN palpites p1 
ON u.iduser = p1.iduser
LEFT JOIN palpites p2
ON u.iduser = p2.iduser
WHERE p1.result <> '' AND p1.result = p1.palpite AND p2.result <> '' AND p2.result <> p2.palpite
GROUP BY u.iduser

The results are coming like this:

iduser - acertos - erros
1      - 16562   - 16562
2      - 16490   - 16490

The right thing would be for the table to come back like this:

iduser - acertos - erros
1      - 170   - 97
2      - 168   - 99

If I put only one Count and only one left Join to pick up only the hits, the table comes correct, but when I add the bugs, it comes as I said above. What am I doing wrong? Thanks in advance.

Answer 1

Jota use a sub-volume (2nd to 5th line) to calculate the error:

SELECT u.iduser, COUNT(p.idpalpite) acertos, 
(SELECT COUNT(e.idpalpite) FROM palpites e 
LEFT usuarios us
ON us.iduser = e.iduser
WHERE e.result <> '' AND e.result <> e.palpite) AS erros
FROM usuarios u
LEFT JOIN palpites p 
ON u.iduser = p.iduser
WHERE p.result <> '' AND p.result = p.palpite
GROUP BY u.iduser