獲取相同 ID 行的結果集百分比

我使用下面提供的查詢獲得下表作為輸出。

;WITH cte AS (
      SELECT c.CaseID AS 'Case #',
      m.ManufacturerName,
      ou.OutcomeName
FROM Consumes con
INNER JOIN [Case] c
ON con.FKCaseID = c.CaseID
INNER JOIN Manufacturer m 
ON m.ManufacturerID = con.FKManufacturerID
INNER JOIN Case_Outcome oc
ON oc.FKCaseID = c.CaseID
INNER JOIN OutCome ou
ON oc.FKOutcomeID = ou.OutcomeID
)

SELECT c.[Case #],
c.ManufacturerName,
      STUFF((SELECT ','+OutcomeName
      FROM cte
      WHERE c.[Case #] = [Case #]
      FOR XML PATH('')),1,1,'') as OutcomeName
FROM cte c
GROUP BY c.[Case #],c.ManufacturerNAme

對於所有製造商，我需要列出由於他們的藥物而發生的 # 個事件以及每個結果發生的百分比。

我怎樣才能得到這個？我使用了下面的查詢，但它沒有給出正確的輸出百分比。

SELECT 
 m.ManufacturerName, 
 COUNT(c.CaseID) AS '# Events',
 COUNT(CASE WHEN ou.OutcomeName = 'Death' THEN c.CaseID ELSE NULL END) /COUNT(c.CaseID)*100.0 AS 'Death Events',
 COUNT(CASE WHEN ou.OutcomeName = 'Hospitalization' THEN c.CaseID ELSE NULL END)/COUNT(c.CaseID)*100.0 AS 'Hospitalization Events',
 COUNT(CASE WHEN ou.OutcomeName = 'Life Threatening' THEN c.CaseID ELSE NULL END)/COUNT(c.CaseID)*100.0 AS 'Life Threatening Events',
 COUNT(CASE WHEN ou.OutcomeName = 'Disability' THEN c.CaseID ELSE NULL END)/COUNT(c.CaseID)*100.0 AS 'Disability Events',
 COUNT(CASE WHEN ou.OutcomeName = 'Congenital Anomaly' THEN c.CaseID ELSE NULL END)/COUNT(c.CaseID)*100.0 AS 'Congenital Anomaly Events',
 COUNT(CASE WHEN ou.OutcomeName = 'Required Intervention' THEN c.CaseID ELSE NULL END)/COUNT(c.CaseID)*100.0 AS 'Required Intervention Events',
 COUNT(CASE WHEN ou.OutcomeName = 'Other Serious' THEN c.CaseID ELSE NULL END)/COUNT(c.CaseID)*100.0 AS 'Other Serious Events'
FROM Consumes con
  INNER JOIN [Case] c ON con.FKCaseID = c.CaseID
  INNER JOIN Manufacturer m ON m.ManufacturerID = con.FKManufacturerID
  INNER JOIN Case_Outcome oc ON oc.FKCaseID = c.CaseID
  INNER JOIN OutCome ou ON oc.FKOutcomeID = ou.OutcomeID
GROUP BY m.ManufacturerName

當所有結果都屬於同一類型時，上述查詢給出 0.0 或 100.0。當同一案例有不同的結果時，此查詢不會按預期工作。我在這裡做錯了什麼？任何幫助將非常感激。

a 的數據類型COUNT是 a[BIG]INT並且整數除法會導致截斷值，零或一。將此乘以 100.0 返回 0.0 或 100.0，因此您只需將計算順序更改為先乘：

100.0 * COUNT(CASE WHEN ou.OutcomeName = 'Death' THEN c.CaseID ELSE NULL END) /COUNT(c.CaseID) AS 'Death Events',

引用自：https://dba.stackexchange.com/questions/146849