Percentage from Total SUM after GROUP BY SQL Server

by

You don’t need a cross join. Just use window functions:

SELECT P.PersonID, SUM(PA.Total),
       SUM(PA.Total) * 100.0 / SUM(SUM(PA.Total)) OVER () AS Percentage
FROM Person P JOIN
     Package PA
     ON P.PersonID = PA.PackageFK
GROUP BY P.PersonID;

Note that you do not need the JOIN for this query:

SELECT PA.PersonID, SUM(PA.Total),
       SUM(PA.Total) * 100.0 / SUM(SUM(PA.Total)) OVER () AS Percentage
FROM Package PA
GROUP BY PA.PersonID;

SQL Server does integer division. I do such calculations using decimal numbers so they make more sense.

Here is a SQL Fiddle, with two changes:

  1. The database is changed to SQL Server.
  2. The total is stored as a number rather than a string.

Leave a Comment