Two SUM functions in Oracle SQL to return two different sums with two SELECT

Asked

Viewed 109 times

2

I am running the following select on Oracle SQL Developer:

SELECT SUM(FANFISCA.TOTAL_NF) AS TOTAL_FATURAMENTO,
FANFISCA.CD_REPRESENTANT COD_REPRESENTANTE,
GEEMPRES.NOME_COMPLETO REPRESENTANTE

FROM FANFISCA,GEEMPRES

WHERE GEEMPRES.CD_EMPRESA = FANFISCA.CD_REPRESENTANT

GROUP BY FANFISCA.CD_REPRESENTANT, GEEMPRES.NOME_COMPLETO


UNION ALL

SELECT SUM(FAPEDIDO.TOTAL_FATURAMENTO) AS TOTAL_PEDIDO,
FAPEDIDO.CD_REPRESENTANT COD_REPRESENTANTE,
GEEMPRES.NOME_COMPLETO REPRESENTANTE

FROM FAPEDIDO,GEEMPRES

WHERE FAPEDIDO.CD_REPRESENTANT = GEEMPRES.CD_EMPRESA

AND FAPEDIDO.CONTROLE BETWEEN '05' AND '15'

GROUP BY GEEMPRES.NOME_COMPLETO,FAPEDIDO.CD_REPRESENTANT

Below is the result of the consultation: inserir a descrição da imagem aqui

I need to return TOTAL_FATURAMENTO and TOTAL_PEDIDO in 2 different columns, as you can see in the image, the query returns all in the same column of TOTAL_FATURAMENTO.

sqldeveloper

1 answer

1


Don’t use UNION because it serves to get data exactly the way you posted and that doesn’t suit you.

Try:

SELECT COD_REPRESENTANTE, REPRESENTANTE, TOTAL_FATURAMENTO, TOTAL_PEDIDO FROM
(SELECT SUM(FANFISCA.TOTAL_NF) AS TOTAL_FATURAMENTO,
            FANFISCA.CD_REPRESENTANT COD_REPRESENTANTE,
            GEEMPRES.NOME_COMPLETO REPRESENTANTE
FROM FANFISCA,GEEMPRES
WHERE GEEMPRES.CD_EMPRESA = FANFISCA.CD_REPRESENTANT
GROUP BY FANFISCA.CD_REPRESENTANT, GEEMPRES.NOME_COMPLETO) foo

FULL OUTER JOIN

(SELECT SUM(FAPEDIDO.TOTAL_FATURAMENTO) AS TOTAL_PEDIDO,
            FAPEDIDO.CD_REPRESENTANT COD_REPRESENTANTE,
            GEEMPRES.NOME_COMPLETO REPRESENTANTE
FROM FAPEDIDO,GEEMPRES
WHERE FAPEDIDO.CD_REPRESENTANT = GEEMPRES.CD_EMPRESA
AND FAPEDIDO.CONTROLE BETWEEN '05' AND '15'
GROUP BY GEEMPRES.NOME_COMPLETO,FAPEDIDO.CD_REPRESENTANT) bar
ON foo.COD_REPRESENTANTE = bar.COD_REPRESENTANTE;

I believe that an ORDER BY is useful for organizing the result.

  • This worked very well, but there is an ambiguity error when we leave COD_REPRESENTANTE, REPRESENTATIVE within the 1st SELECT. When we remove and leave in this way SELECT TOTAL_PEDIDO,TOTAL_FATURAMENTO FROM returns the correct data, but it is clear without the presence of COD_REPRESENTANTE variables, REPRESENTATIVE. Any idea how we can solve this??

  • As they are equal, unless they do not exist in one of the tables, you can use: COALESCE(foo.COD_REPRESENTANTE, bar.COD_REPRESENTANTE), COALESCE(foo.rEPRESENTANTE, bar.REPRESENTANTE).

  • Where will I apply this COALESCE function? Would that be: SELECT TOTAL_PEDIDO,TOTAL_FATURAMENTO, COALESCE(foo.COD_REPRESENTANTE, bar.COD_REPRESENTANTE), COALESCE(foo.REPRESENTATIVE, bar.REPRESENTATIVE) FROM? Or I have to apply that COALESCE below the SELECT of each SUM?

  • In the most external select list: SELECT COALESCE(foo.COD_REPRESENTANTE, bar.COD_REPRESENTANTE) COD_REPRESENTANTE, COALESCE(foo.REPRESENTANTE, bar.REPRESENTANTE) REPRESENTANTE, TOTAL_FATURAMENTO, TOTAL_PEDIDO FROM ...

  • Thank you very much brother! It worked perfect here.

Browser other questions tagged sqldeveloper

You are not signed in. Login or sign up in order to post.