how to find o/p all the student who are enroll in 2 or more courses in sql server using group by?

Question

i have a table name as testtabl1 now
i want to find all the student who are enroll in 2 or more courses,
in this sid is the student id and cid is the course id

table structure

create table testtabl1(Sid int,cid int,years varchar(20))
insert into testtabl1(Sid,cid,years)
select 1,1,'2016'
union all
select 2,2,'2017'
union all
select 1,2,'2017'

new to sql server and stackoverflow need help !!

tried

select sid,COUNT(*),cid from testtabl1 group by sid,cid having count(*)>1

This is a very commonly posted homework assignment. From the [help/on-topic]: *Questions asking for homework help must include a summary of the work you've done so far to solve the problem, and a description of the difficulty you are having solving it.* — Ken White, Apr 03 '19 at 01:35
You have "having" in the tags for the question. Have you tried using it in the query? — Greenstone Walker, Apr 03 '19 at 01:49
sorry i forget to use it done now but still not getting desired o/p — Deepak Jain, Apr 03 '19 at 02:00
What result are you getting? And what result are you expecting? https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Dale K, Apr 03 '19 at 02:39

score 0 · Answer 1 · answered Apr 03 '19 at 06:48

0

SELECT sid as StudentId,
       COUNT(cid) as SelectedCoursesCount
FROM testtabl1
GROUP BY sid
HAVING COUNT(cid) > 1;

answered Apr 03 '19 at 06:48

Stix

455
5
16

how to find o/p all the student who are enroll in 2 or more courses in sql server using group by?

1 Answers1