CODEWITHSUNDEEP
mysqloracle-databasecount
bbb
bbb

Reputation: 1489

Count number of occurrences of one column by a different column value? [Oracle]

I have an Oracle databases exam coming up and I'm going through past papers to study for it. I cannot figure out the answer to this question. The question reads:

"Write an SQL statement to that will list each student's name and the number of project preferences they have"

Here is the entity relation ship diagram for the question:

scrshot

So far I have this code

select student.studentname, studentproject.preferenceno
from student
inner join studentproject
on student.studentid = studentproject.studentid
order by student.studentname;

That prints out the student name and their preferences one by one as rows. What I can't seem to figure out is how to get the count of the number of preferences of each student to appear as a column with the student name beside it. When I have tried to add count() it just gives me one row with a value equivalent to the total number of preferences in the table, rather than the total number of preferences for each student

Here is the SQLFiddle I have been working on http://sqlfiddle.com/#!2/4bc4f1/6

Any help at all is much appreciated. Thanks in advance!

Upvotes: 0

Views: 267

Answers (1)

xQbert
xQbert

Reputation: 35333

Should just be a count. The question is a bit vague however, count by project.. overall count.. distinct count what? and I would change the join to a left join incase there are students with no preferences.

select student.studentname, count(studentproject.preferenceno)
from student
LEFT join studentproject
on student.studentid = studentproject.studentid
GROUP BY student.studentname
order by student.studentname;

Your sample data doesn't seem to matter if the count is distinct or not.

and you don't seem to have any students without preferences so the left join may be moot in your sample data but relevant if you want each student and their count a student without a any preferences is still a student with a count of 0.

Upvotes: 2

Related Questions