SQL query to filter records based on count and status

I have to filter records based on the status when the count is more than 1. Column names: Student_id, Status, term, and course.

DB: Postgres

Condition to filter:

1. If there exists only one record for the student then the status(true or false) does not matter. Fetch the record.
2. If record count for a student is more than one then fetch only those students whose status is true. (More than one record would mean, same Student_id, term, and course). At any given time there will be only one record with status as true.

How do I write the SQL query for this?

Answer

You can try below query:

Create Table #TableA(
id int,
Student_id Varchar(100),
[Status] bit,
term int,
course varchar(10)
)

Insert Into #TableA Values(1, 1, 1, 3, 'C#')
Insert Into #TableA Values(2, 2, 0, 6, 'Php')
Insert Into #TableA Values(3, 2, 0, 6, 'Php')
Insert Into #TableA Values(4, 2, 1, 6, 'Php')
Insert Into #TableA Values(5, 2, 1, 7, 'Php')

Select a.id, a.Student_id, a.Status, a.term, a.course from 
(
Select *, count(*) over (Partition By Student_id, term, course) As row_count From #TableA
) a
Where a.row_count = 1 Or a.[Status] = 1

Result will be like below:

id Student_id Status term course

1 1 1 3 C#

4 2 1 6 Php

5 2 1 7 Php