0

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

12
  • 1
    Even if you can not share your current data, you could build a simulated scenario with random data just for the sake of the question.
    – McNets
    Commented Jan 24, 2019 at 8:15
  • Ok I will try to build some sample data. I am not sure if it will come out correctly. Please wait. Commented Jan 24, 2019 at 8:16
  • I don't know Spark SQL, but you have several columns in your query that are not in an aggregate nor in the GROUP BY clause. Commented Jan 24, 2019 at 8:17
  • You can use a fiddle like dbfiddle.uk, rextester.com or sqlfiddle.com
    – McNets
    Commented Jan 24, 2019 at 8:17
  • @Lennart Exactly. I understand that. But I don't know how to overcome that. Previously I did a UNION for the filter col3 = 8 and col3 <> 8. But I have been told to do without the UNION. So I am stumped currently. Btw, SPARK SQL, I am currently writing only SQL, nothing specific to SPARK or any other vendor. Commented Jan 24, 2019 at 8:20

1 Answer 1

0

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 <> 8 THEN PO.col4::float
             WHEN PO.col3 = 8 
             THEN CASE WHEN (CAST(PO.col4 AS float) - SUM(CAST(PO.col5 AS float))) > 0 
                        AND SUM(CAST(PO.col5 AS float)) > 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 > 0

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

2
  • Thank you for your answer. It follows the same way I think about it. No I apologize, we don't have an expected result. Basically I'm given the "logic" by ppl working with DOMDs and I have to write queries. The query using UNION and the one you have written apparently are wrong according to those people. I can't help it further. I'll take it up with my superior to ask for the business logic. Thank you again. Commented Jan 24, 2019 at 15:56
  • Please have a look at this question here - stackoverflow.com/questions/54351525/… I have shown exactly how the output should be, which I clarified from the person who gave me the logic. I am still a novice so I find it hard to grasp this logic. Commented Jan 24, 2019 at 16:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.