How to use SQL Distinct Properly?

Question

I have table like this:

table supervisor

I want the result like this:

and like this

I use this query, but this doesn't work out

Select Lecturer1,
Count(Student)
From supervisor

I use this also

Select Distinct Lecturer1,
(Select Count(Student) From Supervisor)
From Supervisor
Order By Supervisor.Lecuter1

still doesn't work out.

Answer 1

When using an aggregate property such as Count(), Min(), Max(), Sum() etc.. You will have to 'Group By' any other column you are trying to query.

Such as

  Select Lecturer1,Count(Student)
    From supervisor
  Group by Lecturer1

Distinct will give you the distinct from each column selected in totality.