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:
| Doctor | Patient |
|---|---|
| Carson | Alisson |
| Carson | Alisson |
| Carson | Alisson |
| Carson | Becker |
| Carson | Becker |
| Daniel | Alisson |
| Daniel | Alisson |
| Daniel | David |
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:
| Doctor | Patient | Count(Patient) |
|---|---|---|
| Carson | Alisson | 3 |
| Carson | Becker | 2 |
| Daniel | Alisson | 2 |
| Daniel | David | 1 |
So far, and because I am new to mysql, my code has produced a table that looks like this:
| Doctor | Patient | Count(Patient) |
|---|---|---|
| Carson | Alisson | 5 |
| Carson | Becker | 2 |
| Daniel | David | 1 |
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
>Solution :
Just group by both
select doctor, patient, count(*)
from information
group by doctor, patient