Getting the percentage of a result set for same ID rows I have the below table obtained as an output using the query provided below that.



;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


For all the manufacturers, I need to list down the # events occurred due to their drug and the percentage of each outcome occurred.

How can I get this? I used the below query but it doesn't give the proper output percentage.

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


The above query gives either 0.0 or 100.0 when all the outcomes are of the same type. When there are different outcomes for the same case, this query doesn't work as expected. What am I doing wrong here? Any help would be much appreciated.
The datatype of a COUNT is a [BIG]INT and integer divison results in truncated values, either zero or one. Multiplying this times 100.0 returns  either 0.0 or 100.0, so you just need to change the order of calculation to multiply first:

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