CODEWITHSUNDEEP
sqlsql-serverjoinsumpercentage
Ryan Gadsdon
Ryan Gadsdon

Reputation: 2378

Percentage from Total SUM after GROUP BY SQL Server

I have these results:

PersonID    SUM(PA.Total)
-------------------------
   1            75
   2            75
   3            15
   4            15
   5            60
   6            60

With the table like:

PersonID    Total
------------------
   1         50
   2         50
   3         10
   4         10
   5         40
   6         40
   1         25
   2         25
   3          5
   4          5
   5         20
   6         20

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:

PersonID    SUM(PA.Total)   Percentage
--------------------------------------
   1            75              25%
   2            75              25%
   3            15              5%
   4            15              5%
   5            60              20%
   6            60              20%

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

 SELECT 
     P.PersonID, SUM(PA.Total)
     SUM(PA.Total) * 100 / [p] AS 'Percentage'
 FROM 
     Person P
 JOIN 
     Package PA ON P.PersonID = PA.PackageFK
 CROSS JOIN 
     (SELECT SUM(PA.[Total]) AS [p] 
      FROM Package PA) t
 GROUP BY 
     P.PersonID

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

Upvotes: 37

Views: 106785

Answers (6)

Vishal Singh
Vishal Singh

Reputation: 801

Uncomplicated

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

Without JOIN

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

Upvotes: 1

jabs
jabs

Reputation: 1848

Adding to the selected answer, you can add another variable to further slice the %, like a person's office

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

This will give a total by the office.

Posted as a new answer rather than a comment for better visibility.

Upvotes: 0

KnarfaLingus
KnarfaLingus

Reputation: 466

Like the others said, you can use CTE's to solve the issue, mine is a little different in that I didn't use a CROSS JOIN (no special reason) and did a double CTE. Also I threw a NULLIF in to prevent possible divide by zero errors (will result in NULL instead).

Also, the Package table Total column should be a numeric type (not the specific "numeric" type but a column type that can hold numbers ), any time you are summing or performing math on a column, thats a tip-off it should be numeric.

;WITH PersonTotals AS
(
  SELECT P.PersonID, SUM(CAST(PA.Total AS MONEY)) Total
  FROM Person P
  JOIN Package PA ON P.PersonID = PA.PackageFK
  GROUP BY P.PersonID
),
GrandTotal AS
(
  SELECT SUM(PT.Total) Total
  FROM PersonTotals PT
)
SELECT PT.*, (PT.Total / NULLIF((SELECT Total From GrandTotal),0)) * 100 Percentage
FROM PersonTotals PT

Upvotes: 2

Gowtham Alan
Gowtham Alan

Reputation: 234

You can use Common table Expression...

;with cte as(
  select  P.PersonID,SUM(cast(PA.Total as int))Total  from  Package PA join   Person P on P.PersonID = PA.PackageFK GROUP BY P.PersonID 
  )
  select PersonId,Total,cast (Total * 100 / t.GrandTotal as varchar) + '%'  [Percentage] from cte cross join(select SUM(cast(Total as int)) as GrandTotal from Package)t

Upvotes: 2

Sreenu131
Sreenu131

Reputation: 2516

Try this below code

;With cte(PersonID ,Total)
AS
(
 SELECT  1 ,75 UNION ALL
 SELECT  2 ,75 UNION ALL
 SELECT  3 ,15 UNION ALL
 SELECT  4 ,15 UNION ALL
 SELECT  5 ,60 UNION ALL
 SELECT  6 ,60
 )

SELECT PersonID, 
       Total, 
       CAST(CAST((MAX(total)OVER(partition BY personid ORDER BY total)*100.0/ 
       MaxSum) AS INT)AS VARCHAR(5))+ '%' AS Percentage 
FROM   (SELECT personid, 
               total, 
               stotal, 
               Max(stotal) 
                 OVER( 
                   ORDER BY stotal DESC) AS MaxSum 
        FROM   (SELECT personid, 
                       total, 
                       Sum(total) 
                         OVER( 
                           ORDER BY personid) AS STotal 
                FROM   Cte)dt 
        GROUP  BY dt.personid, 
                  dt.total, 
                  dt.stotal)dt2 
ORDER  BY dt2.personid ASC

Result

PersonID    Total   Percentage
------------------------------
1            75         25%
2            75         25%
3            15         5%
4            15         5%
5            60         20%
6            60         20%

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269763

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.

Upvotes: 89

Related Questions