How to COUNT a field not directly related to the where expression on ORACLE

I am trying to find out How many students are enrolled in all units, that are associated with a particular course.

I have tried.

SELECT        COUNT(studentID) AS Expr1
FROM            Course CROSS JOIN
                         Enrolment
WHERE        (Course.courseCode = 'S4000')

But i am getting 3 , each time for the diffe开发者_JAVA百科rent course codes.

It should be 1 for S4000. I have 3 students in total.

---

You didn't tell us columns names, so I create:

SELECT cu.courseCode, COUNT(e.StudentID) AS tot_Students
FROM COURSEUNIT cu INNER JOIN ENROLMENT e
ON cu.unitCode = e.couseCode    
GROUP BY cu.courseCode    

---

Its because of what happens in cross join each record in 1st table is get related other record 2nd table .in your case suppose there are 3 student and 3 courses

Student table

Id | Name

1 | abc

2 | pqr

3 | xyz

Courses

Code | Name

S4000 | c1

S4001 | c2

S4002 | c3

Cross join table

Student.Id | Student.Name | Courses.code |courses.name

1| abc| S4000 | c1

2 | pqr| S4000 | c1

3 | xyz| S4000 | c1

1| abc| S4001 | c2

2 | pqr| S4001 | c2

3 | xyz| S4001 | c2

1| abc| S4002 | c3

2 | pqr| S4002 | c3

3 | xyz| S4002 | c3

Now you can see their are 3 records created for each courses so you are getting 3 answer each time.

so in your query you should have same common field to get required records such as there will Fourier key relationship

you add common field in where clause like

SELECT COUNT(studentID) AS Expr1

FROM Course CROSS JOIN Enrolment WHERE (Course.courseCode = 'S4000')

        and Course.studentID=Enrolment.studentID

OR you can use inner join on common field