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