Add data from two tables with same parameters

Question

I have two tables, titlospagar and titlosavulsos and in both I have the field scoreMotorista and I would like to add up the values of each

I would like to do a query, gathering their data in a certain time, giving GROUP BY in placaMotorista.

I made the following two queries, but the values return wrong (too big for each plate).

SELECT SUM(a.valorTitulo) AS geralPlaca, SUM(b.valorTitulo) AS manutencaoPlaca, b.placaMotoristaTitulo AS placa
    FROM titulospagar a, titulosavulsos b
    WHERE a.dataVencimentoTitulo BETWEEN '$dataDe' AND '$dataAte' && b.dataTitulo BETWEEN '$dataDe' AND '$dataAte' && a.placaMotoristaTitulo = b.placaMotoristaTitulo && b.placaMotoristaTitulo != 'Nenhum'
    GROUP BY a.placaMotoristaTitulo, b.placaMotoristaTitulo
    ORDER BY a.placaMotoristaTitulo ASC, b.placaMotoristaTitulo ASC





SELECT SUM(titulospagar.valorTitulo) AS geralPlaca, SUM(titulosavulsos.valorTitulo) AS manutencaoPlaca, titulospagar.placaMotoristaTitulo AS placa
    FROM titulospagar INNER JOIN titulosavulsos ON titulospagar.placaMotoristaTitulo != 'Nenhum' && titulospagar.placaMotoristaTitulo = titulosavulsos.placaMotoristaTitulo &&  titulospagar.dataVencimentoTitulo BETWEEN '$dataDe' AND '$dataAte' && titulosavulsos.dataTitulo BETWEEN '$dataDe' AND '$dataAte'
    GROUP BY titulospagar.placaMotoristaTitulo, titulosavulsos.placaMotoristaTitulo
    ORDER BY titulospagar.placaMotoristaTitulo ASC

In both returns the same value for each board, but the return value is wrong.

Sqlfiddle link, demonstrating the problem: LINK

If anyone can help me, I’d appreciate it!!

Answer 1

Taking into account only existing boards in both tables, titulospagar and titulosavulsos:

SELECT      A.placa
        ,   A.valorTitulosPagar
        ,   B.valorTitulosAvulsos
FROM        (
                SELECT      placa
                        ,   SUM(valor) AS valorTitulosPagar
                FROM        titulospagar
                WHERE       data BETWEEN '2019-01-01' AND '2019-01-31' 
                GROUP BY    placa
            ) A
LEFT JOIN   (
                SELECT      placa
                        ,   SUM(valor) AS valorTitulosAvulsos
                FROM        titulosavulsos
                WHERE       data BETWEEN '2019-01-01' AND '2019-01-31' 
                GROUP BY    placa
            ) B ON B.placa = A.placa
ORDER BY    A.placa

Taking into account all existing boards in the table placas (perhaps the most correct option, because there is the board EEE-4444 that only exists in the table titulosavulsos and shall not be taken into account in query above):

SELECT      A.placas
        ,   A.valorTitulosPagar
        ,   B.valorTitulosAvulsos
FROM        (
                SELECT      p.placa
                        ,   SUM(IFNULL(TP.valor, 0)) AS valorTitulosPagar
                FROM        placas          P
                LEFT JOIN   titulospagar    TP  ON  TP.placa = P.placa 
                                                AND TP.data BETWEEN '2019-01-01' AND '2019-01-31' 
                GROUP BY    P.placa
            ) A
LEFT JOIN   (
                SELECT      p.placa
                        ,   SUM(IFNULL(ta.valor, 0)) AS valorTitulosAvulsos
                FROM        placas          P
                LEFT JOIN   titulosavulsos  TA  ON  TA.placa = P.placa 
                                                AND TA.data BETWEEN '2019-01-01' AND '2019-01-31' 
                GROUP BY    P.placa
            ) B ON B.placa = A.placa
ORDER BY    A.placa