Stack Overflow Asked by Daniele Arrighi on January 31, 2021
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
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;
Answered by Gordon Linoff on January 31, 2021
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP