How to count occurrences of this dataset in SQL?-CodePudding

Hello I need to know the sql code on this question:

How many students did not participate for the first time the exam DBS21 in periode 201101?

studentnr	examcode	periode
101	DBS21	201001
101	DBS21	201003
101	DBS21	201101
101	ICB21	201001
102	ICB21	201001
103	DBS21	201001
103	DBS21	201003
104	DBS21	201101
104	ICB21	201003
105	DBS21	201003
105	DBS21	201101
105	ICB21	201003

The answer should be 2 times

CodePudding user response：

You can use the conditional aggregation approach to achieve the desired result -

SELECT COUNT(DISTINCT studentnr) - SUM(CASE WHEN periode = 201101 THEN 1 ELSE 0 END)
  FROM your_table;

This query will perfectly work when there is no duplication for periode = 201101 for a single studentnr which is as per your sample data.

CodePudding user response：

I hope the below code helps:

select count(*)
from
(select a.studentnr, min(a.periode) as min_period
 from
 (select *
  from your_table_name
  where studentnr in ( select distinct studentnr
                       from your_table_name
                       where examcode = "DBS21" and
                             periode = "201101"
                     ) and
  examcode = "DBS21")a
group by 1)b
where b.min_period <> "201101";

Output: 2