Sid Sid - 6 months ago 8
SQL Question

Skip or Filter records from a group if any row has null for an id

I am trying to eliminate or filter or skip any group that has a null in any columns of that group.

Following is a sample dataset.

courseId personId pointsWon MobileTitle
a1 alice 10 bluTest1
a1 alice null bluTest9
a1 batman 5 bluTest1
a1 batman 5 bluTest9
b2 robocop 1 test1
b2 robocop null test2
b2 madHatter 5 test1
b2 madHatter 2 test2
c3 batman 25 GOTHAM1
c3 batman 25 GOTHAM2
c3 superman 90 GOTHAM1
c3 superman null GOTHAM2


From the above dataset, I want to sum pointsWon of every personId. If any pointsWon has a null, then that personId needs to be skipped or filtered from the courseId. Below is a sample dataset that I have been trying to fetch.

courseId personId Total_pointsWon
a1 batman 10
b2 madHatter 7
c3 batman 50


I have tried filtering based on Not Null, NVL etc, but am unable to filter or skip the user record completely from the courseId if any points inside that course is null for that user.

Your help would be very much appreciated.

Edit 1 :-
PN: The Datasets provided is just a sample made from a large dataset. I cannot hardcode any values in my query to get the desired output.

Answer

You can try something like this:

with test(courseId, personId, pointsWon, MobileTitle) as (
 select 'a1','alice',       10      ,'bluTest1' from dual union all    
 select 'a1','alice',       null    ,'bluTest9' from dual union all
 select 'a1','batman',      5       ,'bluTest1' from dual union all
 select 'a1','batman',      5       ,'bluTest9' from dual union all
 select 'b2','robocop',     1       ,'test1'    from dual union all
 select 'b2','robocop',     null    ,'test2'    from dual union all
 select 'b2','madHatter',   5       ,'test1'    from dual union all
 select 'b2','madHatter',   2       ,'test2'    from dual union all
 select 'c3','batman',      25      ,'GOTHAM1'  from dual union all
 select 'c3','batman',      25      ,'GOTHAM2'  from dual union all
 select 'c3','superman',    90      ,'GOTHAM1'  from dual union all
 select 'c3','superman',    null    ,'GOTHAM2'  from dual 
)
select courseId, personId, sum(pointsWon)
from test
group by courseId, personId
having count(1) = count(pointsWon)

This simply makes the sum and, at the same time counts the rows (count(1)) and the rows where pointsWon is not null (count(pointsWon)) and then checks if the two values are equal