how to retrieve records which occurred more than twice in oracle?

Question

I have this table

create table student (
   stu_id int,
   s_name nvarchar(max),
   s_subject nvarchar(max),
)

and this as data

insert into student values(123,'pammy','English');
insert into student values(123,'pammy','Maths');
insert into student values(123,'pammy','Chemistry');
insert into student values(124,'watts','Biology');
insert into student values(125,'Tom','Physics');
insert into student values(125,'Tom','Computer';
insert into student values(125,'Tom','ED';

so i wanted to retrieve records which has occurred more than twice. my code is

select stu_id,s_Name 
from student 
group by stu_id,s_Name 
having count(stu_id) >2 ;

the result was perfect.

but when i want s_subject as well it says no rows selected. I dont know why.

select stu_id,s_Name,s_subject 
from student 
group by stu_id,s_Name,s_subject 
having count(stu_id) >2 ;

Answer 1

It's because none of your students have more than one record per subject.

select stu_id,s_Name,s_subject 
from student 
group by stu_id,s_Name,s_subject 
having count(stu_id) >2 ;

This code asks for records that occur more than twice that have the same Student ID, name and subject. None of the records in your sample meet this.

If, however, what you actually want is the ID, name and subjects of any student that is taking more than two classes, this can accomplished quite easily.

Using a slightly modified version of your initial SQL as a filter, we get this:

select stu_id, name, subject
from student
where stu_id in (   select stu_id 
                    from student 
                    group by stu_id 
                    having count(stu_id) >2 );

Hope this helps.