CODEWITHSUNDEEP
sql
Sergei Walankov
Sergei Walankov

Reputation: 135

Conditional percentage column in SQL table

I want to add a column showing percentage values for Score, in which each record's score is divided by the sum of the scores for the category it's in. So the values in the new column should be 0.6, 0.4, 0.3 and 0.7.

Id   Category   Score
1    foo        6        
2    foo        4          
3    bar        30
4    bar        70

The best I can think to do is to create a temporary table that creates sum values for Score using GROUP BY and then JOIN-ing it to the main table. Is there a more efficient way of doing this?

Upvotes: 0

Views: 352

Answers (2)

Suresh Gajera
Suresh Gajera

Reputation: 362

You can also do it without creating temporary table.

SELECT A.* , CAST(Score*1.0/TotalScore AS DECIMAL(6,2))
FROM [table1] A 
JOIN (
        SELECT      Category
                    ,SUM(Score) TotalScore 
        FROM        [table1] 
        GROUP BY    Category
      ) B
ON    A.Category = B.Category

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Use window functions:

select t.*,
       score * 1.0 / sum(score) over (partition by category) as newcol
from t;

The * 1.0 is because some databases do integer division.

Upvotes: 1

Related Questions