CODEWITHSUNDEEP
mysqlsqlsql-server
Nayana
Nayana

Reputation: 1549

CALCULATE PERCENTAGE IN SQL by group

I pulled out data from a database like this (simplified for the sake of simplicity), I'd like to add a column called "percentage".

ID   GRADE   PERCENTAGE
 1     10        10/(10+20) * 100 -- THIS PART SHOULD BE SHOWN IN DIGIT
 1     20           .
 2     15           15/(15+24+16) * 100
 2     24
 2     16
 3     29
 4     96
 4     159
 .     .
 .     .
 .     .

thank you in advance

Upvotes: 2

Views: 9729

Answers (4)

kgu87
kgu87

Reputation: 2057

select
a.id, a.grade
, case
    when b.total = 0 then 0
    else a.grade/b.grade * 100
  end as percentage
from
data a
cross apply (
   select sum(tot.grade) as total
   from data tot where a.id = tot.id
) b

Upvotes: 0

Vincent Pan
Vincent Pan

Reputation: 256

Using the OVER(PARTITION BY ), you won't need a join in SQL Server

SELECT *,  100*Grade/ SUM(Grade) OVER(PARTITION BY ID) AS Percentage
FROM (
     VALUES(1, 10),(1,20), (2, 15), (2,24), (2,16), (3,29)
) your_table (ID, GRADE)

Upvotes: 4

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521239

SELECT t1.ID,
       t1.GRADE,
       100.0 * (t1.GRADE / t2.gradeSum) AS PERCENTAGE   -- important: multiply
FROM yourTable t1                                       -- by 100.0 here
INNER JOIN
(
    SELECT ID, SUM(GRADE) AS gradeSum
    FROM yourTable
    GROUP BY ID
) t2

Upvotes: 0

juergen d
juergen d

Reputation: 204766

The inner select gets the garde sum of the ids. 

select t.id, t.grade, t.grade * 100 / gsum
from your_table t
join
(
   select id, sum(grade) as gsum
   from your_table
   group by id
) tmp on tmp.id = t.id

Upvotes: 3

Related Questions