Select statement using Group-by while ignoring column on Postgresql

I have a table name “report” in Postgresql db like:

Student Class Marks Observation_time
A        1     11    21/7/2020
A        2     13    18/7/2020   
B        1     19    17/7/2020
A        1     17    15/7/2020
B        1     15    21/7/2020
C        1     NAN     10/7/2015
C        1     NAN     11/7/2015
C        2     8     10/7/2015
C        2     0     11/7/2015
D        1     NAN     10/7/2015
D        1     NAN     11/7/2015
D        2     NAN     10/7/2015
D        2     NAN     11/7/2015

I want to obtain all the rows from above table for which marks were always NAN (irrespective of observation_time) for particular student and class.

Expected output is:

student class
C       1
D       1
D       2

Could someone please help me with the query? Thanks

Answer

if you want to find all rows with NULL in Marks, use:

SELECT DISTINCT Student,Class
FROM report
WHERE Marks IS NULL;

..the DISTINCT operator removes duplicates from result

another variant is:

SELECT Student,Class
FROM report
GROUP BY Student,Class
HAVING COUNT(*)=COUNT(*)FILTER(WHERE Marks IS NULL)

Advertisement

Answer