Sum rows with same Id based on type and exlude where SUM = 0

I have this table MOVEMENTS:

Id  |  FatherId  |  MovementType |   Quantity   |
=================================================
1   | A         |   IN          |   10          |
2   | A         |   IN          |   5           |
3   | A         |   OUT         |   5           |
4   | B         |   IN          |   10          |
5   | B         |   OUT         |   10          |
6   | C         |   IN          |   5           |

I’m trying to get all the FatherId with the SUM of IN – OUT Movments > 0. So the result would be:

FatherId    |   Total   |
=========================
A           |     10    |
C           |     5     |

FatherId = B not showing because SUM(MovementType = IN) – SUM (MovementType = OUT) = 0

I tried with

SELECT FatherId,
  (SELECT (
    SUM(CASE WHEN MovementType = 'IN' THEN Quantity ELSE 0 END) -
    SUM(CASE WHEN MovementType = 'OUT' THEN Quantity ELSE 0 END)
  )) AS Total
  FROM MOVEMENTS
  GROUP BY FatherId
  ORDER BY FatherId

That gives me the result grouped by FatherId, but I’m not able to filter with Total > 0, and also, I’m unable to put this query in a Subquery like:

SELECT * FROM MOVEMENTS WHERE FatherId IN (SELECT ....) OFFSET ... FETCH NEXT ... ROWS ONLY

Is this doable without a stored procedure? Thank you for any help

Answer

Why are you using a subquery? This should do what you want:

SELECT FatherId,
       (SUM(CASE WHEN MovementType = 'IN' THEN Quantity ELSE 0 END) -
        SUM(CASE WHEN MovementType = 'OUT' THEN Quantity ELSE 0 END)
       ) AS Total
FROM MOVEMENTS
GROUP BY FatherId
HAVING (SUM(CASE WHEN MovementType = 'IN' THEN Quantity ELSE 0 END) -
        SUM(CASE WHEN MovementType = 'OUT' THEN Quantity ELSE 0 END)
       ) > 0;

You can also simplify the logic to use a single SUM():

SELECT FatherId,
       SUM(CASE WHEN MovementType = 'IN' THEN Quantity
                WHEN MovementType = 'OUT' THEN - Quantity
                ELSE 0
           END) AS Total
FROM MOVEMENTS
GROUP BY FatherId
HAVING SUM(CASE WHEN MovementType = 'IN' THEN Quantity
                WHEN MovementType = 'OUT' THEN - Quantity
                ELSE 0
           END) > 0
ORDER BY FatherId;