A Simple Way to Do Percentage By Status

By January 13, 2011Tips

This view tells us the % of orders that use shipper 1 by employee. The ratio analysis divides a SUM by a COUNT of the same field and then uses a CAST to turn it into a percentage on a floating point scale.

CREATE VIEW OpenPercentage
SELECT LastName, CAST(SUM(CASE ShipVia 
WHEN 1 THEN 1 ELSE 0 END) 
AS FLOAT)/COUNT(ShipVia),COUNT(ShipVia) AS [Count]
FROM [dbo].[Orders]
JOIN [dbo].[Employees] 
ON [dbo].[Employees].[EmployeeID]=[dbo].[Orders].[EmployeeID]
GROUP BY LastName