0
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
Welcome to Stackoverflow. The
Union
joins the results, does not make the sum. You will need to use theSUM
or relate the two queries and make the sum. Tried to do instead of two queries, only one?– Clarck Maciel
Hi Clark Maciel! Thanks for the reply! I couldn’t put logic together in one query only. They end up duplicating values and other occurrences in the tests I did. Then I got the integrity values using UNION, however, without being able to add them. I tried to treat the sum in PHP to play on a Charts.js chart, but, I’m not evolving...
– Rodrigo Moura
You will always use with this field
AND a.carcod = 621
? Or you intend to change that value at some point?– Clarck Maciel
He will always be fixed.
– Rodrigo Moura