1

I generated a CTE called mycte from 5 select statements using union to combine them. The output looks like this for a particular job:

ID JOB_ID STATUS BASE_ID PERCENTAGE
20DA GBR01 0 12 20
21DA GBR01 0 12 30
21DA GBR01 0 14 50

For every unique JOB_ID the sum of the percentage must be 100%.

To test my CTE, I used:

SELECT JOB_ID, SUM(PERCENTAGE) AS myTOTAL
FROM myCTE
GROUP BY JOB_ID
HAVING SUM(PERCENTAGE) <> 100
ORDER BY SUM(PERCENTAGE)

The output showed that not all sum up to 100 because of dirty data in the database. I then attempted to extract 2 different tables, one for PERCENTAGE = 100% and the other for <> 100%.

Since the columns I needed to extract for the new table are ID, JOB_ID, STATUS, BASE_ID and PERCENTAGE, I then applied

SELECT
    ID, JOB_ID, STATUS, BASE_ID, PERCENTAGE,
    SUM(percentage) OVER (PARTITION BY JOB_ID, BASE_ID, ID) AS PERCENTAGE_SUM
FROM
    mycte

Unfortunately where clause will not work on window function.

Question: how do I extract only ID, JOB_ID, STATUS, BASE_ID, PERCENTAGE from mycte where sum of the percentage = 100?

Salman A
  • 262,204
  • 82
  • 430
  • 521
Temi K
  • 13
  • 3
  • 2
    *Please* when posting actually look at the preview of it before hitting the Submit/Post Question answer; if you had you would have seen quite how malformed your content was. – Thom A Mar 08 '22 at 12:02
  • But you don't have a CTE - don't misuse terminology. You have a table named mycte that you perhaps generated using a CTE but it is NOT a CTE. – SMor Mar 08 '22 at 12:52

1 Answers1

1

Looking at the sample data it looks like you need to partition by JOB_ID only:

WITH mycte AS (
    ...
), cte2 as (
    SELECT
        ID, JOB_ID, STATUS, BASE_ID, PERCENTAGE,
        SUM(percentage) OVER (PARTITION BY JOB_ID) AS PERCENTAGE_SUM 
    FROM mycte
)
SELECT *
FROM cte2
WHERE PERCENTAGE_SUM = 100
Salman A
  • 262,204
  • 82
  • 430
  • 521