CODEWITHSUNDEEP
sqlpostgresql
Amiclone
Amiclone

Reputation: 408

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

Upvotes: 0

Views: 671

Answers (3)

hussein zakizadeh
hussein zakizadeh

Reputation: 173

I created an example for your question.

CREATE TABLE reportt (
  Class  int,
  Marks  int,
  Student  VARCHAR(100),
  Observation_time VARCHAR(100),
);



INSERT INTO reportt
  (Student, Class, Marks,Observation_time)
VALUES
  ('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,null,'10/7/2015'),
  ('C',1,null,'11/7/2015'),
  ('C',2,8,'10/7/2015'),
  ('C',2,0,'11/7/2015'),
  ('D',1,null,'10/7/2015'),
  ('D',1,null,'11/7/2015'),
  ('D',2,null,'10/7/2015'),
  ('D',2,null,'11/7/2015')
  ;

  with CTE_select as (
    select ISNULL(Marks,0) Marks, Student,Class 
    from  reportt 
  )
  select Student,Class,SUM(Marks) from CTE_select
  where marks >= 0
  group by Class,Student
  having   SUM(Marks)= 0;

result =

Student Class
C        1  
D        1  
D        2

Upvotes: 1

Игорь Тыра
Игорь Тыра

Reputation: 955

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)

Upvotes: 0

Arron
Arron

Reputation: 916

Check out the docs about GROUP BY, it is very powerful but it can be rather tricky as well. The previous answer (DISTINCT) is actually a sort of GROUP BY as well. I think this should get you the result you are looking for, but please read the docs in order to understand what is happening.

Select MIN(Student), MIN(Class)
  from report
where Marks = 0
  group by Student, Class

Upvotes: 0

Related Questions