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

How many students did participate the examcode DBS21 more than twice?

This is the R code of it:

  beoordeling %>% 
  filter(examcode == "DBS21", !is.na(resultaat)) %>% 
  group_by(studentnr) %>% 
  filter(n() > 2) %>% 
  summarise()

I'll give a smaller version of the dataset, (I removed a column called 'resultaat' since it's not important for the question.

Thanks alot!

The output should be 1

CodePudding user response：

Use conditional aggregation:

SELECT studentnr
FROM yourTable
GROUP BY studentnr
HAVING COUNT(CASE WHEN examcode = 'DBS21' THEN 1 END) > 2;

You could also use a filtering approach:

SELECT studentnr
FROM yourTable
WHERE examcode = 'DBS21'
GROUP BY studentnr
HAVING COUNT(*) > 2;

CodePudding user response：

use where filter for DBS21

select studentnr from table_name
where examcode='DBS21'
group by studentnr
having count(1)>2