Group and exclude rows that had empty values aggregated

I have a oracle sql table that looks like so

"STUDENT_ID","FULL_NAME","SEMESTER_ID","STIP_ID"
"1","Liam Bottrill","1","1"
"1","Liam Bottrill","2","3"
"1","Liam Bottrill","3","2"
"1","Liam Bottrill","4","5"
"2","Maurits Smitham","1","6"
"2","Maurits Smitham","2",""
"2","Maurits Smitham","3","2"
"2","Maurits Smitham","4","6"
"43","Jackie Cotton","1",""
"43","Jackie Cotton","2",""
"43","Jackie Cotton","3",""
"43","Jackie Cotton","4",""

I want to group this table by "STUDENT_ID" and exclude from result any students that have any of "STIP_ID" rows empty

Im aiming for result like this:

"STUDENT_ID","FULL_NAME"
"1","Liam Bottrill"

Liam Bottrill should be displayed while Maurits Smitham and Jackie Cotton should be excluded from result

Can you please help me with such aggregate function?

CodePudding user response：

Here is one way, using aggregation:

SELECT *
FROM yourTable
WHERE STUDENT_ID IN (
    SELECT STUDENT_ID
    FROM yourTable
    GROUP BY STUDENT_ID
    HAVING COUNT(CASE WHEN STIP_ID IS NULL THEN 1 END) = 0
);

Another way, using exists logic:

SELECT t1.*
FROM yourTable t1
WHERE NOT EXISTS (
    SELECT 1
    FROM yourTable t2
    WHERE t2.STUDENT_ID = t1.STUDENT_ID AND
          t2.STIP_ID IS NULL
);

CodePudding user response：

You can group by the identifier and then use conditional aggregation to find the student where the count when STIP_ID is NULL (which, in Oracle, is the same as an empty string):

SELECT student_id,
       MAX(full_name) AS full_name
FROM   table_name
GROUP BY student_id
HAVING COUNT(CASE WHEN stip_id IS NULL THEN 1 END) = 0;

Which, for your sample data:

CREATE TABLE table_name (STUDENT_ID, FULL_NAME, SEMESTER_ID, STIP_ID) AS
SELECT  1, 'Liam Bottrill',   1, 1 FROM DUAL UNION ALL
SELECT  1, 'Liam Bottrill',   2, 3 FROM DUAL UNION ALL
SELECT  1, 'Liam Bottrill',   3, 2 FROM DUAL UNION ALL
SELECT  1, 'Liam Bottrill',   4, 5 FROM DUAL UNION ALL
SELECT  2, 'Maurits Smitham', 1, 6 FROM DUAL UNION ALL
SELECT  2, 'Maurits Smitham', 2, NULL FROM DUAL UNION ALL
SELECT  2, 'Maurits Smitham', 3, 2 FROM DUAL UNION ALL
SELECT  2, 'Maurits Smitham', 4, 6 FROM DUAL UNION ALL
SELECT 43, 'Jackie Cotton',   1, NULL FROM DUAL UNION ALL
SELECT 43, 'Jackie Cotton',   2, NULL FROM DUAL UNION ALL
SELECT 43, 'Jackie Cotton',   3, NULL FROM DUAL UNION ALL
SELECT 43, 'Jackie Cotton',   4, NULL FROM DUAL;

Outputs:

STUDENT_ID FULL_NAME

1 Liam Bottrill

STUDENT_ID	FULL_NAME
1	Liam Bottrill

db<>fiddle here