1

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

Umesh Shreeman
  • 47
  • 6
  • Did you try searching for any help on your own? [This](https://stackoverflow.com/questions/1841426/is-it-possible-to-group-by-multiple-columns-using-mysql) took me 30 seconds to find. – Isaac May 31 '22 at 23:21
  • I did not know until after I had written my question. The moment I posted it, I found this link after googling 'how to do group by with 2 columns': https://stackoverflow.com/questions/2421388/using-group-by-on-multiple-columns. I can delete this thread or keep it here. What do you suggest? – Umesh Shreeman May 31 '22 at 23:28

1 Answers1

0

Just group by both

select doctor, patient, count(*)
from information
group by doctor, patient
Hogan
  • 69,564
  • 10
  • 76
  • 117
  • @UmeshShreeman -- you are welcome -- on this website if a question helps you up vote it. If it answers your question you click on the check mark. – Hogan May 31 '22 at 23:30
  • 1
    I upvoted your answer and clicked the check mark! Edit: Apparently, I need 15 reputation to cast a vote but I have clicked on it anyway(website says feedback will be recorded), in case the upvote does not show up. – Umesh Shreeman May 31 '22 at 23:32