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 &lt;&gt; 8 THEN PO.col4
       WHEN PO.col3 = 8 
            THEN CASE
                      WHEN (ROUND(CAST(PO.col4 AS double) - SUM(CAST(PO.col5 AS double)), 2)) &gt; 0 
                            AND SUM(CAST(PO.col5 AS double)) &gt; 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 &gt; 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) &gt; 0. If yes then check col4 - SUM(col5) &gt; 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
FWIW, I think you will be way better of separating the 2 cases for PO.col3, and combine the results with a UNION (which I assume is what you had before). Meanwhile, I tried to fix your query so that it compiles in your fiddle:

SELECT * FROM (
    SELECT
        PO.col1,
        PO.col2,
        CASE WHEN PO.col3 &lt;&gt; 8 THEN PO.col4::float
             WHEN PO.col3 = 8 
             THEN CASE WHEN (CAST(PO.col4 AS float) - SUM(CAST(PO.col5 AS float))) &gt; 0 
                        AND SUM(CAST(PO.col5 AS float)) &gt; 0
                       THEN (CAST(PO.col4 AS float) - SUM(CAST(PO.col5 AS float)))
                       END
             END AS Quantity
    FROM mytable AS PO
    GROUP BY PO.col1, PO.col2, PO.col3, PO.col4 
) AS T
WHERE Quantity &gt; 0


I have no idea whether it is correct or not, for that I would need a small sample and an expected result.