Say, in mysql, I have a column doctor and a column patient. One doctor can have multiple patients and one patient can have multiple doctors. Here is an example table:

I would like the code to produce a table which shows the doctor, his patient and how many times the patient appeared with this doctor. This is what it would look like:

So far, and because I am new to mysql, my code has produced a table that looks like this:

As you can see, my code assigns a patient to one doctor. In this case, Alisson is assigned to Carson even though Alisson is a patient of Daniel as well.

Here is what the code looks like:

    select doctor, patient, count(*) from information
    group by patient

I assume that I get the wrong table because I am grouping by patient. However, in order to receive the desired table, I would need to group by both patient and doctor, which I do not know how to do. Can anyone help me?

Thank you!

Umesh

CodePudding user response：

Just group by both

select doctor, patient, count(*)
from information
group by doctor, patient