Percentage from Total SUM after GROUP BY SQL Serve

问题:

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

回答1:

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.

回答2:

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

回答3:

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%

回答4:

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