How do I build two selects on different columns?

I´m trying to create a table with two different selects in two different columns. What SQL command can I use?

Obs: “NAME_FAB” means Name Manufacturer.

Expected result:
NAME_FAB            Y           X
manufacturer1      100.0      20.0
manufacturer2       80.0      10.0
manufacturer3      120.0       5.0

--COLUMN Y
SELECT PRODUCT.NAME_FAB, SUM(NFOUT_ITENS.TOTAL_VALUE) AS Y
FROM NFOUT
    INNER JOIN NFOUT_ITENS
    ON NFOUT_ITENS.IDOUT = NFOUT.COD
    INNER JOIN PRODUCT
    ON PRODUCT.COD = NFOUT_ITENS.IDPRODUCT
WHERE NFOUT.DT_EMIT BETWEEN '2019-07-01' AND '2019-07-31'
AND NFOUT.FINAL = 'NORMAL'
GROUP BY PRODUCT.NAME_FAB

**HERE**

--COLUMN X
SELECT PRODUCT.NAME_FAB, SUM(NFOUT_ITENS.TOTAL_VALUE) AS X
FROM NFOUT
    INNER JOIN NFOUT_ITENS
    ON NFOUT_ITENS.IDOUT = NFOUT.COD
    INNER JOIN PRODUCT
    ON PRODUCT.COD = NFOUT_ITENS.IDPRODUCT
WHERE NFOUT.DT_EMIT BETWEEN '2019-07-01' AND '2019-07-31'
AND NFOUT.FINAL = 'DEVOL'
GROUP BY PRODUCT.NAME_FAB

Answer

You want conditional aggregation:

SELECT p.NAME_FAB,
       SUM(CASE WHEN o.FINAL = 'DEVOL' THEN i.TOTAL_VALUE END) AS X,
       SUM(CASE WHEN o.FINAL = 'NORMAL' THEN i.TOTAL_VALUE END) AS Y
FROM NFOUT o JOIN
     NFOUT_ITENS i
     ON i.IDOUT = s.COD JOIN
     PRODUCT p
     ON p.COD = i.IDPRODUCT
WHERE o.DT_EMIT BETWEEN '2019-07-01' AND '2019-07-31' 
GROUP BY p.NAME_FAB;

Your FROM clauses do not have NFOUT so I added it.

Advertisement

Answer