Home > Software engineering >  How to count occurrences of this dataset in SQL?
How to count occurrences of this dataset in SQL?

Time:04-18

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

  •  Tags:  
  • sql
  • Related