返回绝对数字，并共同返回百分比

Question

我正在尝试从医院数据库中计算[访问]的总数星期。因此，我想知道是否有人可以帮助我查询，因为我仍在学习SQL。

Goal Table format:
 - Date (prefer dd/mm/yyyy)
 - Patient_Name (e.g John)
 - Patient_Id (e.g 12345)
 - Visits 
 - Professionals (Categorical Variables: Nurse, Doctor, Assistant Nurse)

因此，我想获得一个查询，可以在一周内列出护士在特定日期范围内的总访问和所有专业人员的总访问百分比。例如，护士访问患者(约翰(15次，助理护士访问10次，而医生薪水访问5次，所以我的最后一张桌子是：

      ____________________________________________
     |____Date_____|__Prof__|__Visits_|___Percen__|
     |06/01/2018   | Nurse  |   15    |   0.5     |
     |02/11/2017   | A-Nurse|   10    |   0.33    |
     |19/04/2016   | Nurse  |    5    |   0.16    |
     |

以下是我在SSM上的SQL声明，因为根据患者的需求，我使用了专业人士数据的案例声明，有时治疗师访问而不是护士/医生，因此我希望该部分是动态的：

SELECT  CONVERT(VARCHAR(10), [myDate], 101), SUM([visits]) AS [Date] , [Professionals], ((SELECT [Visits] from MyHospitalTable)* 100 / (Select SUM([Visits]) From MyHospitalTable)) as Percen
FROM
(SELECT 
   Count(*) as [total],
   [Date] as [myDate],
   [Patient_id] as [myPatient_Id],
   [Patient_Name] as [myPatient_Name],
   [visits] as [visits],
   CASE
          WHEN [Professionals] LIKE '%Nurse%' THEN 'Nurse'
          WHEN [Professionals] LIKE '%Therapist%' THEN 'Therapy'
          else 'Unknown'
          END AS [Professionals]
   FROM [MyHospitalTable]
 ) a
GROUP BY [myDate]

我知道我的查询不正确，需要改进，如果有人可以帮助我获取数据，那真是太棒了。

预先感谢。

Answer 1

您可以使用窗口函数来计算总数并找到百分比。

下面给您一个想法。不过，它与您的桌子并不完全兼容。

;with ct as (
    select MyDate, sum(Visits) Visits
      , count(Visits) over (partition by MyDate order by MyDate) TotalVisits
    from HospitalTable
    group by MyDate
)
select MyDate, Visits, (Visits * 100 / TotalVisits)
from ct