Normalize values within a group using SQL

Question

I will need your help with this one. I want to normalize numbers within groups. Say I have this dataset:

I want to group these rows by A and B, then normalize the values of C within its group, i.e. summing up all C's of the group and then dividing each C by that sum. In the above example, I would expect this result:

A  B   C
---------
0  1   1            // sum of C's in group is 1, 1/1=1
1  1   0            // sum of C's in group is 6, 0/6=0
1  1  1/3           // ...
1  1  2/3           // ...
1  2  1/2           // sum of C's in group is 2, 1/2=1/2
1  2  1/2           // ...

Division by zero can be handled separately. How to do this using SQL (or PSQL, if that helps)? I can think of ways to do this in principle, but I always end up with deeply nested SELECTs, which I want to avoid.

Thanks in advance!

Lukasz Szozda · Accepted Answer · 2015-12-29 15:07:00Z

10

You could use windowed functions for it:

SELECT a,b,
    1.0 * c / CASE WHEN SUM(c) OVER(PARTITION BY a,b) = 0 THEN 1
                   ELSE SUM(c) OVER(PARTITION BY a,b) END AS c
FROM tab

SqlFiddleDemo

Output:

╔════╦════╦════════════════════╗
║ a  ║ b  ║         c          ║
╠════╬════╬════════════════════╣
║ 0  ║ 1  ║ 1                  ║
║ 1  ║ 1  ║ 0                  ║
║ 1  ║ 1  ║ 0.3333333333333333 ║
║ 1  ║ 1  ║ 0.6666666666666666 ║
║ 1  ║ 2  ║ 0.5                ║
║ 1  ║ 2  ║ 0.5                ║
║ 2  ║ 2  ║ 0                  ║           -- added for check division by 0
╚════╩════╩════════════════════╝

edited Dec 29, 2015 at 15:07

answered Dec 29, 2015 at 15:03

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lukasz Szozda · Accepted Answer · 2015-12-29 15:00:53Z

2

You can use a derived table to aggregate the values and then join the results back to the original table. To avoid division by 0, a where clause has been included. For these 0 sum cases, you might have to include a special condition to select them.

select t.a, t.b, 1.0 * t.c/t1.total_c
from tablename t 
join (select a, b, sum(c) as total_c
      from tablename
      group by a, b) t1
on t.a = t1.a and t.b = t1.b
where t1.total_c > 0

edited Dec 29, 2015 at 15:00

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

answered Dec 29, 2015 at 14:56

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

2 Comments

random6174 Over a year ago

Lets test that... hang on :)

Lukasz Szozda Over a year ago

I've upgraded it a bit t.total_c should be t1.total_c and Results are 0 for all rows(int division) demo :)

Clodoaldo Neto · Accepted Answer · 2015-12-29 15:17:09Z

0

with t (a,b,c) as (values
    (0,  1,  3),
    (1,  1,  0),
    (1,  1,  2),
    (1,  1,  4),
    (1,  2,  1),
    (1,  2,  1),
    (2,  2,  0)
)
select
    a, b,
    c::float / coalesce(nullif(sum(c) over(partition by a, b), 0), 1) as normal
from t
;
 a | b |      normal       
---+---+-------------------
 0 | 1 |                 1
 1 | 1 |                 0
 1 | 1 | 0.333333333333333
 1 | 1 | 0.666666666666667
 1 | 2 |               0.5
 1 | 2 |               0.5
 2 | 2 |                 0

edited Dec 29, 2015 at 15:17

answered Dec 29, 2015 at 15:05

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Collectives™ on Stack Overflow

Normalize values within a group using SQL

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related