UNION is not consolidating SOMA between 2 Postgresql tables

Question

I’m new here and I’m looking for a solution to this problem. I have two tables that add production values per hour of two different teams, however, with the UNION occurs the duplication of lines with the time that contains values.

Ex. Result of query A

Result of query B

Result of query with UNION between the two tables

I need the coincident hours values to be presented consolidated (summed).

SELECT DISTINCT
   TO_char(a.acodatinc,'HH24') AS hora,
   SUM(ap.acoparvallan) AS valor_acordo
FROM acordo a
 INNER JOIN acordo_parcela ap on ap.acocod = a.acocod
 INNER JOIN usuario u on u.usucod = a.acousuinc
 INNER JOIN devedor d ON d.devcod = a.devcod
WHERE a.acodatinc::DATE = current_date-1
AND a.acoati = 0
AND ap.acoparati = 0
AND d.devsal > 0
AND ap.acoparnum = 1
AND a.carcod = 621
AND u.usunom NOT ILIKE '%1987%'
AND u.usunom NOT ILIKE '%105%'
AND u.usunom NOT ILIKE '%463%'
AND u.usunom NOT ILIKE '%1992%'
AND u.usunom NOT ILIKE '%2014%'
GROUP BY TO_CHAR(a.acodatinc,'HH24')

 UNION 

SELECT
 x.hora_excecao::TEXT AS hora,
 SUM(x.valor) AS valor_acordo

FROM (
 SELECT DISTINCT

 (SELECT
   CASE 
   WHEN x.carqueevecod = 3 THEN x.carresiteval
   END AS valor
   FROM carteira_resposta_item x
   WHERE x.carcod = cri.carcod
   AND x.devcod = cri.devcod
   AND x.carqueevecod = 3
   ORDER BY carresdat DESC LIMIT 1
 ) AS valor,

 (SELECT
   CASE 
   WHEN x.carqueevecod = 7 THEN x.carresiteint::TEXT
   END AS vencimento
   FROM carteira_resposta_item x
   WHERE x.carcod = cri.carcod
   AND x.devcod = cri.devcod
   AND x.carqueevecod = 7
   ORDER BY carresdat DESC LIMIT 1
 ) AS hora_excecao

 FROM carteira_questionario_evento cqe,
   carteira_questionario_evento_o cqeo,
   carteira_resposta_item cri,
   carteira_resposta cr,
   devedor d
 WHERE cqe.carqueevecod = cqeo.carqueevecod
 AND cri.carqueevecod = cqe.carqueevecod
 AND cqe.carcod = cri.carcod
 AND cr.carcod = cri.carcod
 AND cr.devcod = cri.devcod
 AND d.carcod = cri.carcod
 AND d.devcod = cri.devcod
 AND cri.carcod = 621
 AND  cri.carresdat = current_date-1
)x
GROUP BY x.hora_excecao
ORDER BY hora

Answer 1

The UNION should not be used to "consolidate sum", but to join sets. After joining, you can use Group By to group the results and function sum to add the desired fields.
The ideal is to seek to generate a single query that relates the tables involved and generate the expected result through a common table that relates the two queries, in this case I observed that the table devedor exists in both consultations and may become the link between the two consultations.
Since you already have the queries that generate the partial results, you can create two views(acordo_parcela and acordo_carteira), use the UNION ALL to join the items of the sets and add them together.
The Union disregard equal tuples in such a way that it would disregard if there were two identical tuples in each set, then no Union there would be only one tuple as a result, action that would make the sum value incorrect in your situation. No union all there will be the two tuples which, in their situation, is necessary, because they are distinct sets where the values should not be suppressed in the case of the existence of coincidence of the records of each set.

Select hora, sum(valor_acordo) as valor_total from (
  Select hora, valor_acordo from acordo_parcela
  Union All
  Select hora, valor_acordo from acordo_carteira
) acordos
group by hora

This is just one way to do on the basis of union (union) results already obtained through your queries. You can also use joins to relate the views/tables involved and sum the values of the fields of each view.

For more details on:

• The use of Joins, consult: What is the difference between INNER JOIN and OUTER JOIN?
• Difference between Union and Union All, consult: What’s the difference between UNION and UNION ALL?
• Views, consult: What are SQL views? What advantages and disadvantages to using?