SQL Server- SUM Subquery each other-CodePudding

I would like to ask for a little help in SQL Server.

How can I sum each other subquery and display it into one column.

I tested to add the subqueries in the SELECT statement, but, then I realize that I don't know how to sum between them, then I added them to the FROM statement to see If I adding them up in the SELECT statement could but work, but it didn't.

the idea is to substract B with A for each CodUbic.

'A' for purchases ($),

'B' for returned products ($)

SELECT MONTH(FechaE) AS Mes, YEAR(FechaE) AS Ano, CodUbic, TipoFac, (SumFacA SumFacB)

FROM 
    dbo.SAFACT,
    (SELECT SUM(Monto) FROM dbo.SAFACT WHERE TipoFac IN ('A')) AS SumFacA,
    (SELECT -SUM(Monto) FROM dbo.SAFACT WHere TipoFac IN ('B')) AS SumFacB

WHERE
    TipoFac IN ('A', 'B')
GROUP BY  
    MONTH(FechaE),
    YEAR(FechaE),
    CodUbic,
    TipoFac

ORDER BY 
    YEAR(FechaE) DESC, 
    MONTH(FechaE);

Expected Result:

    Mes   Ano    CodUbic            TotalSum
   ----------------------------------------------------
     1    2022    0002-1     #### (Due the sum of A-B)   
     1    2022    0004-1     #### (Due the sum of A-B)
     2    2022    0002-1     #### (Due the sum of A-B)
     2    2022    0004-1     #### (Due the sum of A-B) 
    ...    ...     ...           ...

CodePudding user response：

You are finally showing the expected result. It doesn't contain a column for TipoFac. This means, you want a result row per month and CodUbic, not per month, CodUbic, and TipoFac. Change your GROUP BY clause accordingly.

You get the difference between A and B with conditional aggregation (CASE WHEN inside the aggregation function).

SELECT
    MONTH(FechaE) AS Mes, YEAR(FechaE) AS Ano, CodUbic,
    SUM(CASE WHEN TipoFac = 'A' THEN monto ELSE -monto END) AS diff
FROM 
    dbo.SAFACT
WHERE
    TipoFac IN ('A', 'B')
GROUP BY  
    MONTH(FechaE), YEAR(FechaE), CodUbic
ORDER BY 
    YEAR(FechaE) DESC, MONTH(FechaE), CodUbic
;

CodePudding user response：

Simply:

SELECT 
    MONTH(FechaE) AS Mes,
    YEAR(FechaE) AS Ano,
    CodUbic,
    SUM(CASE WHEN TipoFac = 'A' THEN ISNULL(Monto,0) ELSE 0 END) -
    SUM(CASE WHEN TipoFac = 'B' THEN ISNULL(Monto,0) ELSE 0 END)

FROM 
    dbo.SAFACT

WHERE
    TipoFac IN ('A', 'B')
GROUP BY  
    MONTH(FechaE),
    YEAR(FechaE),
    CodUbic
    
ORDER BY 
    YEAR(FechaE) DESC, 
    MONTH(FechaE);