sql query to count and display full name

So I am very confused by this query to display teachers’ full name. I have used teacher_name ||’ ‘|| teacher_lastname, but when I try to do so for the students, something happens. I am trying to get number of times a teacher had an appointment with a student and display teacher fullname and students full name. Below is my query :

select d.teacher_id ||' '|| d.teacher_lastname as Teacher,  count (distinct c.student_id)
from teacher d inner join class t on t.teacher_id=d.teacher_id
inner join classRoom tp on tp.dest_id=t.dest_id
inner join classFl ta on tp.dest_id=ta.dest_id
inner join students c on c.student_id=ta.student_id
group by d.teacher_id,d.teacher_lastname

This is the output, but when I add

select d.teacher_id ||' '|| d.teacher_lastname as Teacher,
c.student_name||' '||c.student_lastname as Student,
count (distinct c.student_id)

and also add it to the group by d.teacher_id,d.teacher_lastname,c.customer_name,c.customer_lastname

it gives the following result

What is wrong? How can I fix it to have the same nr of counts for each teacher? I am using oracle sql developer

Answer

I think you need to remove the DISTINCT from inside the COUNT. You say you want “nr of times a teacher had an appointment with a student” which I interpret to mean that if teacher T saw student S 9 times you want:

T S 9

If you keep DISTINCT, then grouping on student name and counting the number of unique student IDs will only ever produce a 1 unless two students have identical names. In essence then, by keeping the DISTINCT you are counting “the number of different students with name X that that teacher met with” and mostly this is 1 because “1 unique student named Hana Baker met with xxxx yyyy”, “1 unique student with name Dream Kenise met with xxxx yyyy” ….

If you do have students with the same name but a different ID, then you should add student ID to the GROUP BY to provide distinction between the two students. You don’t have to add it to the SELECT, but you’ll struggle to tell them apart if you do. If you have two students both called S, but one has ID 1 (and he saw T 5 times) and the other has ID 2 (and she saw T 4 times) you’ll get a result of:

T S 5
T S 4

You might want to add other columns to your select to better tell the difference between them

---

In your first query, using DISTINCT meant “the number of different students that sought a meeting with the teacher”. Omitting DISTINCT in that query would have counted “the number of times the teacher had a meeting with any student”