如何计算每个病人预订多少医生？ [英] How to count how many doctors are booked by each patient?

问题描述

我需要的结果如下：

  PatientID医生
 Patient1 3 
 Patient2 2 
 Patient3 1 
  

预订的表格如下

  GPS表
 
 PatientID DoctorID DATE 
 Patient1医生1 2016-02-16 
 Patient1医生1 2016-04-08 
 Patient1 Doctor2 2016-06-09 
 Patient2 Doctor3 2017-01-02 
 Patient2 Doctor6 2016-12-01 
 Patient3 Doctor1 2016-07-12 
  

还有更多的预订，但我只是给这个表作为例子。
如果这个人为医生预订了2次，我还需要确保它不会算同一个医生。

我现在的代码是：

 选择Bookings.PatientID，count（Bookings.DoctorID）as Book from Bookings where Bookings.DoctorID;感谢您的帮助！
 
 
 
 < > 
 选择患者，计数（*）
从（
选择不同的Bookings.PatientID作为患者，DoctorID作为医生
从预订）作为t 
 
由患者分组; 
  
 
I need the results to look like:
PatientID   Doctors
Patient1    3
Patient2    2
Patient3    1
The booked table looks like this
GPS Table

PatientID   DoctorID  DATE
Patient1    Doctor1   2016-02-16
Patient1    Doctor1   2016-04-08
Patient1    Doctor2   2016-06-09
Patient2    Doctor3   2017-01-02
Patient2    Doctor6   2016-12-01
Patient3    Doctor1   2016-07-12
There are more bookings, but I am just giving this table as an example.
Also I need to make sure that it would not count the same doctor if the person is booked 2 times for the doctor.

The code I have right now is:
select Bookings.PatientID, count(Bookings.DoctorID) as Doctors from Bookings where Bookings.DoctorID;
Thank you for any help!
解决方案
Use Group by over a temp table with distinct patient and doctor
 select Patient, count(*)
 from (
 select distinct Bookings.PatientID as Patient ,DoctorID as Doctors 
 from Bookings  ) as t

 Group by Patient;


                        
这篇关于如何计算每个病人预订多少医生？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！