Skip to content
Advertisement

Percentage from Total SUM after GROUP BY SQL Server

I have these results:

With the table like:

These are grouped by the person. Now I’m looking to add a column with the percentages for each person calculated from the total of all of their sums.

For example: the total sum is 300, and hence I need a result like this:

I have looked at code online and I have come up with a fix such as this:

But I’m unsure how to incorporate the cross join into the join as well as the already group/sum section. Or whether this is along the right lines altogether.

Any help would be appreciated – SQL fiddle http://sqlfiddle.com/#!9/80f91/2

Advertisement

Answer

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

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

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.
User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement