Select statement using Group-by while ignoring column on Postgresql

Question

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

hussein zakizadeh · Accepted Answer · 2020-07-25 08:36:18Z

1

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

edited Jul 25, 2020 at 8:36

user330315

answered Jul 23, 2020 at 3:14

hussein zakizadeh

1731 silver badge8 bronze badges

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

5 Comments

Amiclone Over a year ago

Sorry I didn't specify in question but marks can be negative also.

Amiclone Over a year ago

No. only zeros. I want pair of student and class where he always been reported as 0

Gordon Linoff Over a year ago

@hosseinzakizadeh . . . The correct expression would be something like having min(marks) = 0 and max(marks) = 0.

Amiclone Over a year ago

Sorry for this. But as I am proceeding with my data my requirements got change. Could you please look into the edited code please?

hussein zakizadeh Over a year ago

hi.ok . I will.

Игорь Тыра · Accepted Answer · 2020-07-25 08:33:24Z

0

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)

edited Jul 25, 2020 at 8:33

answered Jul 25, 2020 at 8:06

Игорь Тыра

9755 silver badges12 bronze badges

2 Comments

Amiclone Over a year ago

This will also give C 2 in output. which is not required

Игорь Тыра Over a year ago

added second variant

Arron · Accepted Answer · 2020-07-23 02:51:49Z

0

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

answered Jul 23, 2020 at 2:51

Arron

9166 silver badges15 bronze badges

2 Comments

Amiclone Over a year ago

This will also give C 2 in output. which is not required.

Arron Over a year ago

try removing the Class from the group by, please read the docs about GROUP BY

Collectives™ on Stack Overflow

Select statement using Group-by while ignoring column on Postgresql

3 Answers 3

5 Comments

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related