Union of select’s to return only 1 query

Question

We need to perform the following queries in only 1 select:

1st SELECT

SELECT SUM(FAPEDIDO.TOTAL_PEDIDO) AS TOTAL_CAR,
            FAPEDIDO.CD_REPRESENTANT COD_REPRESENTANTE,
            GEEMPRES.NOME_COMPLETO REPRESENTANTE
FROM FAPEDIDO,GEEMPRES
WHERE FAPEDIDO.CD_REPRESENTANT = GEEMPRES.CD_EMPRESA
AND FAPEDIDO.CONTROLE LIKE '02'
AND FAPEDIDO.DT_PEDIDO BETWEEN TO_DATE(:data_posicao_sintetica_inicial, 'DD/MM/YYYY') AND TO_DATE(:data_posicao_sintetica_final, 'DD/MM/YYYY')
GROUP BY GEEMPRES.NOME_COMPLETO,FAPEDIDO.CD_REPRESENTANT

2nd SELECT

SELECT SUM(FANFISCA.TOTAL_NF) AS TOTAL_FAT,
            FANFISCA.CD_REPRESENTANT COD_REPRESENTANTE,
            GEEMPRES.NOME_COMPLETO REPRESENTANTE
FROM FANFISCA,GEEMPRES
WHERE GEEMPRES.CD_EMPRESA = FANFISCA.CD_REPRESENTANT
AND FANFISCA.DT_EMISSAO BETWEEN TO_DATE(:data_posicao_sintetica_inicial, 'DD/MM/YYYY') AND TO_DATE(:data_posicao_sintetica_final, 'DD/MM/YYYY')
AND FANFISCA.ESPECIE_NOTA LIKE 'S'
AND FANFISCA.CFOP BETWEEN '5101' AND '6404'
GROUP BY FANFISCA.CD_REPRESENTANT, GEEMPRES.NOME_COMPLETO

3rd SELECT

SELECT SUM(FAPEDIDO.TOTAL_PEDIDO) AS TOTAL_PED,
            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'
AND FAPEDIDO.DT_PEDIDO BETWEEN TO_DATE(:data_posicao_sintetica_inicial, 'DD/MM/YYYY') AND TO_DATE(:data_posicao_sintetica_final, 'DD/MM/YYYY')
GROUP BY GEEMPRES.NOME_COMPLETO, FAPEDIDO.CD_REPRESENTANT

We need to return a query in table this way:

ANY IDEA HOW WE CAN DESIGN THIS SELECT TO RETURN TO THE TABLE?

Answer 1

It is noticeable that the COD_REPRESENTANTE is always the same and you intend to search for 3 information from the same code only in different tables, I recommend using the concept of SUBSELECT and use LEFT JOIN to unify the information, the following example:

SELECT cons1.COD_REPRESENTANTE, cons1.REPRESENTANTE, cons3.TOTAL_PED, cons2.TOTAL_FAT, cons1.TOTAL_CAR

--CONSULTA 1 (TOTAL_FAT)
FROM(SELECT SUM(FANFISCA.TOTAL_NF) AS TOTAL_FAT,
     FANFISCA.CD_REPRESENTANT COD_REPRESENTANTE,
     GEEMPRES.NOME_COMPLETO REPRESENTANTE
     FROM FANFISCA,GEEMPRES
     WHERE GEEMPRES.CD_EMPRESA = FANFISCA.CD_REPRESENTANT
     AND FANFISCA.DT_EMISSAO BETWEEN TO_DATE(:data_posicao_sintetica_inicial, 'DD/MM/YYYY') AND TO_DATE(:data_posicao_sintetica_final, 'DD/MM/YYYY')
     AND FANFISCA.ESPECIE_NOTA LIKE 'S'
     AND FANFISCA.CFOP BETWEEN '5101' AND '6404'
     GROUP BY FANFISCA.CD_REPRESENTANT, GEEMPRES.NOME_COMPLETO) cons1

--CONSULTA 2 (TOTAL_CAR)
LEFT JOIN (SELECT SUM(FAPEDIDO.TOTAL_PEDIDO) AS TOTAL_CAR,
                FAPEDIDO.CD_REPRESENTANT COD_REPRESENTANTE,
                GEEMPRES.NOME_COMPLETO REPRESENTANTE
    FROM FAPEDIDO,GEEMPRES
    WHERE FAPEDIDO.CD_REPRESENTANT = GEEMPRES.CD_EMPRESA
    AND FAPEDIDO.CONTROLE LIKE '02'
    AND FAPEDIDO.DT_PEDIDO BETWEEN TO_DATE(:data_posicao_sintetica_inicial, 'DD/MM/YYYY') AND TO_DATE(:data_posicao_sintetica_final, 'DD/MM/YYYY')
    GROUP BY GEEMPRES.NOME_COMPLETO,FAPEDIDO.CD_REPRESENTANT) cons2
            
ON cons1.COD_REPRESENTANTE = cons2.COD_REPRESENTANTE

--CONSULTA 3 (TOTAL_PED)
LEFT JOIN (SELECT SUM(FAPEDIDO.TOTAL_PEDIDO) AS TOTAL_PED,
                        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'
            AND FAPEDIDO.DT_PEDIDO BETWEEN TO_DATE(:data_posicao_sintetica_inicial, 'DD/MM/YYYY') AND TO_DATE(:data_posicao_sintetica_final, 'DD/MM/YYYY')
            GROUP BY GEEMPRES.NOME_COMPLETO, FAPEDIDO.CD_REPRESENTANT) cons3
            
ON cons1.COD_REPRESENTANTE = cons3.COD_REPRESENTANTE

For each result I used the SUBSELECT to "simulate" as an existing table, then crossed through the LEFT JOIN to bring the results in a single line.

I used the LEFT JOIN because not all consultations contain results for all representatives.

Answer 2

A solution can be by UNION , these solutions can generate slow queries however.

SELECT SUM(TOTAL_CAR) TOTAL_CAR,
       SUM(TOTAL_FAT) TOTAL_FAT,
       SUM(TOTAL_PED) TOTAL_PED,
       COD_REPRESENTANTE,
       REPRESENTANTE
FROM
(       
SELECT      SUM(FAPEDIDO.TOTAL_PEDIDO) AS TOTAL_CAR,
            0 AS TOTAL_FAT,
            0 AS TOTAL_PED,
            FAPEDIDO.CD_REPRESENTANT COD_REPRESENTANTE,
            GEEMPRES.NOME_COMPLETO REPRESENTANTE
FROM FAPEDIDO,GEEMPRES
WHERE FAPEDIDO.CD_REPRESENTANT = GEEMPRES.CD_EMPRESA
AND FAPEDIDO.CONTROLE LIKE '02'
AND FAPEDIDO.DT_PEDIDO BETWEEN TO_DATE(:data_posicao_sintetica_inicial, 'DD/MM/YYYY') AND TO_DATE(:data_posicao_sintetica_final, 'DD/MM/YYYY')
GROUP BY GEEMPRES.NOME_COMPLETO,FAPEDIDO.CD_REPRESENTANT
UNION ALL
SELECT      0 AS TOTAL_CAR,
            SUM(FANFISCA.TOTAL_NF) AS TOTAL_FAT,            
            0 AS TOTAL_PED,
            FANFISCA.CD_REPRESENTANT COD_REPRESENTANTE,
            GEEMPRES.NOME_COMPLETO REPRESENTANTE
FROM FANFISCA,GEEMPRES
WHERE GEEMPRES.CD_EMPRESA = FANFISCA.CD_REPRESENTANT
AND FANFISCA.DT_EMISSAO BETWEEN TO_DATE(:data_posicao_sintetica_inicial, 'DD/MM/YYYY') AND TO_DATE(:data_posicao_sintetica_final, 'DD/MM/YYYY')
AND FANFISCA.ESPECIE_NOTA LIKE 'S'
AND FANFISCA.CFOP BETWEEN '5101' AND '6404'
GROUP BY FANFISCA.CD_REPRESENTANT, GEEMPRES.NOME_COMPLETO
UNION ALL
SELECT      0 AS TOTAL_CAR,
            0 AS TOTAL_FAT,
            SUM(FAPEDIDO.TOTAL_PEDIDO) AS TOTAL_PED,
            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'
AND FAPEDIDO.DT_PEDIDO BETWEEN TO_DATE(:data_posicao_sintetica_inicial, 'DD/MM/YYYY') AND TO_DATE(:data_posicao_sintetica_final, 'DD/MM/YYYY')
GROUP BY GEEMPRES.NOME_COMPLETO, FAPEDIDO.CD_REPRESENTANT
) VIRTUAL 
GROUP BY COD_REPRESENTANTE,
       REPRESENTANTE