Duplicate values in SQL query

April 24, 2023

I have this query

SELECT
    facturas.total AS facturas_total,
    notas_credito.total AS notas_credito_total,
    facturas.total - notas_credito.total AS total_dinero
FROM
    (SELECT
         main_venta.periodo AS periodo,
         SUM(main_venta.total) AS total
     FROM  
         main_contribuyente
     LEFT JOIN 
         main_venta ON main_venta.emisor_id = main_contribuyente.id
     LEFT JOIN 
         main_documentotipo ON main_venta.dte_id = main_documentotipo.id
     WHERE 
         main_contribuyente.id = 1
         AND EXTRACT(YEAR FROM to_date(to_char(main_venta.periodo, '999999'), 'YYYYMM')) = 2023
         AND main_documentotipo.codigo IN (30, 32, 33, 34, 110, 901, 904, 909, 910)
     GROUP BY 
         main_venta.periodo) AS facturas,
    (SELECT
         main_venta.periodo AS periodo,
         SUM(main_venta.total) AS total
     FROM 
         main_contribuyente
     LEFT JOIN 
         main_venta ON main_venta.emisor_id = main_contribuyente.id
     LEFT JOIN 
         main_documentotipo ON main_venta.dte_id = main_documentotipo.id
     WHERE 
         main_contribuyente.id = 1
         AND EXTRACT(YEAR FROM to_date(to_char(main_venta.periodo, '999999'), 'YYYYMM')) = 2023
         AND main_documentotipo.codigo IN (112, 60, 61)
     GROUP BY 
         main_venta.periodo) AS notas_credito

When I execute the query, it shows me the sum and period correctly.

This query

SELECT
    main_venta.periodo AS periodo,
    SUM(main_venta.total) AS total
FROM 
    main_contribuyente
LEFT JOIN 
    main_venta ON main_venta.emisor_id = main_contribuyente.id
LEFT JOIN 
    main_documentotipo ON main_venta.dte_id = main_documentotipo.id
WHERE 
    main_contribuyente.id = 1
    AND EXTRACT(YEAR FROM to_date(to_char(main_venta.periodo, '999999'), 'YYYYMM')) = 2023
    AND main_documentotipo.codigo IN (30, 32, 33, 34, 110, 901, 904, 909, 910)
GROUP BY 
    main_venta.periodo

enter image description here

and this query

SELECT
    main_venta.periodo as periodo,
    SUM(main_venta.total) as total
FROM 
    main_contribuyente
LEFT JOIN 
    main_venta ON main_venta.emisor_id = main_contribuyente.id
LEFT JOIN 
    main_documentotipo ON main_venta.dte_id = main_documentotipo.id
WHERE 
    main_contribuyente.id = 1
    AND EXTRACT(YEAR FROM to_date(to_char(main_venta.periodo, '999999'), 'YYYYMM')) = 2023
    AND main_documentotipo.codigo IN (112, 60, 61)
GROUP BY 
    main_venta.periodo

enter image description here

but when I execute the main query, it shows me this:

enter image description here

>Solution :

You would do a FULL OUTER JOIN in case that the periodos are not present in both tables, maybe a LEFT JOIN or an INNER JOIN would be enough but that you must test

select
    COALESCE(facturas.periodo, notas_credito.periodo) as periodo
    facturas.total as facturas_total,
    notas_credito.total as notas_credito_total,
    facturas.total - notas_credito.total as total_dinero
from(
    select
        main_venta.periodo as periodo,
        SUM(main_venta.total) as total
    FROM main_contribuyente
    LEFT JOIN main_venta ON main_venta.emisor_id = main_contribuyente.id
    LEFT JOIN main_documentotipo ON main_venta.dte_id = main_documentotipo.id
    WHERE main_contribuyente.id = 1
        and EXTRACT(YEAR FROM to_date(to_char(main_venta.periodo, '999999'), 'YYYYMM')) = 2023
        and main_documentotipo.codigo IN (30,32,33,34,110,901,904,909,910)
    GROUP BY main_venta.periodo
)as facturas
FULL OUTER JOIN 
(
    select
        main_venta.periodo as periodo,
        SUM(main_venta.total) as total
    FROM main_contribuyente
    LEFT JOIN main_venta ON main_venta.emisor_id = main_contribuyente.id
    LEFT JOIN main_documentotipo ON main_venta.dte_id = main_documentotipo.id
    WHERE main_contribuyente.id = 1
        and EXTRACT(YEAR FROM to_date(to_char(main_venta.periodo, '999999'), 'YYYYMM')) = 2023
        and main_documentotipo.codigo IN (112,60,61)
        GROUP BY main_venta.periodo
) as notas_credito
ON facturas.periodo = notas_credito.periodo