Filtering on window functions in SQL Server [duplicate]

Question

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	BASE_ID	PERCENTAGE
20DA	GBR01	12	20
21DA	GBR01	12	30
21DA	GBR01	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?

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
– Thom A ♦, Commented Mar 8, 2022 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
– SMor, Commented Mar 8, 2022 at 12:52

Salman Arshad · Accepted Answer · 2022-03-08 12:12:04Z

2

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

answered Mar 8, 2022 at 12:12

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Filtering on window functions in SQL Server [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related