How to use case and filter with a group by statement and having an aggregate calculation?

I would like to do the following SELECT PO.col1, PO.col2, CASE WHEN PO.col3 8 THEN PO.col4 WHEN PO.col3 = 8 THEN CASE WHEN (ROUND(CAST(PO.col4 AS double) - SUM(CAST(PO.col5 AS double)), 2)) > 0 AND SUM(CAST(PO.col5 AS double)) > 0 THEN ROUND(CAST(PO.col4 AS double) - SUM(CAST(PO.col5 AS double)), 2) END END AS Quantity FROM my_table AS PO GROUP BY PO.col1, PO.col2 HAVING Quantity > 0 I apologize for not being able to provide sample data/columns. I am not allowed to share my work data. The logic which I have been told to implement is as follows. For each unique pair combination of col1, col2, get a constructed attribute called Quantity. Quantity = col4 wherever col3 = 8. If col3 != 8, then check SUM(col5) > 0. If yes then check col4 - SUM(col5) > 0. If yes, then keep the record, else skip. Out of my own observation I checked that COUNT(DISTINCT col4) for each unique pair of col1 and col2 is 1. I hope that's helpful answering my question. We are working with Spark SQL so I don't know what tag to use for this in the question. SAMPLE DATA