Advanced SUM query

Question

I have the following query:

    SELECT jogador.pont from jogador,usuarios WHERE email='like' and jogador.id=usuarios.j1 
UNION 
SELECT jogador.pont from jogador,usuarios WHERE email='like' and jogador.id=usuarios.j2
UNION
SELECT jogador.pont from jogador,usuarios WHERE email='like' and jogador.id=usuarios.j3 
UNION
SELECT jogador.pont from jogador,usuarios WHERE email='like' and jogador.id=usuarios.j4

which gives me the following result:

pont 
0
10
19
43

i just want to sum these results! tried using sum() this way but didn't work:

SELECT SUM(
SELECT jogador.pont from jogador,usuarios WHERE email='like' and jogador.id=usuarios.j1 
UNION 
SELECT jogador.pont from jogador,usuarios WHERE email='like' and jogador.id=usuarios.j2
UNION
SELECT jogador.pont from jogador,usuarios WHERE email='like' and jogador.id=usuarios.j3 
UNION
SELECT jogador.pont from jogador,usuarios WHERE email='like' and jogador.id=usuarios.j4
    ) AS totalpont

am i doing the SUM query wrong? if so, how can i do it right?

Answer 1

You missed the syntax.

This will work.

SELECT SUM(point) AS totalpont
      FROM
    (SELECT jogador.pont from jogador,usuarios WHERE email='like' and jogador.id=usuarios.j1 
    UNION 
    SELECT jogador.pont from jogador,usuarios WHERE email='like' and jogador.id=usuarios.j2
    UNION
    SELECT jogador.pont from jogador,usuarios WHERE email='like' and jogador.id=usuarios.j3 
    UNION
    SELECT jogador.pont from jogador,usuarios WHERE email='like' and jogador.id=usuarios.j4) T

Hope this helps

Answer 2

Make the result using UNION as a sub-query and then do the sum.

Query

SELECT SUM(t.pont) as totalPont from
(
    SELECT jogador.pont from jogador,usuarios 
    WHERE email='like' and jogador.id=usuarios.j1 
    UNION 
    SELECT jogador.pont from jogador,usuarios 
    WHERE email='like' and jogador.id=usuarios.j2
    UNION
    SELECT jogador.pont from jogador,usuarios 
    WHERE email='like' and jogador.id=usuarios.j3 
    UNION
    SELECT jogador.pont from jogador,usuarios 
    WHERE email='like' and jogador.id=usuarios.j4
)t;