Question 236251 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to do SQL calculations based from different rows with the same ID2

I’m using Postgress DB in AWS and creating some views with calculations.

I have multiple rows with the same ID but different Units and Subunits.
I would like to get a Sum based on Value_1 and Value_2 in the SUM column.
Value_1 and Value_2 are always identical within the same ID but sometimes they are NULL.

I need a formula to calculate the SUM based on the ID.
The SUM will be identicall for all the rows with the same ID.

Input:

ID unit sub_unit value_1 value_2 sum
1 A 1 NULL 10 NULL
1 A 2 NULL 10 NULL
1 A 3 NULL 10 NULL
1 A 4 NULL 10 NULL
1 B 1 5 NULL NULL
1 B 2 5 NULL NULL
1 B 3 NULL NULL NULL
1 B 4 NULL NULL NULL

I have tryed with this querry but since the values are in different rows it results always in NULL

Selcet ID, unit, sub_unit, value_1, value_2, value_1 * value_2 AS sum FROM t1

I’m not sure what would be best practice to solve this problem.
Should I try to fill all the NULL fields with one of the values? This should result in giving me the SUM for each row (SUM will be the same for each row with the same ID)
¨
How can I get the first not NULL value from a column based on an ID and fill the other fields with the same ID with this value?

Maybe something like this?:

ID unit sub_unit value_1 value_2 sum
1 A 1 5 10 50
1 A 2 5 10 50
1 A 3 5 10 50
1 A 4 5 10 50
1 B 1 5 10 50
1 B 2 5 10 50
1 B 3 5 10 50
1 B 4 5 10 50
Tags:

2

Answers


  1. 0

    I would suggest that you use window functions, straightforward and easy to read.

    select id, unit, sub_unit, 
  first_value(value_1) over (partition by id order by value_1 nulls last) value1,
  first_value(value_2) over (partition by id order by value_2 nulls last) value2,
  sum(value_1) over (partition by id) + sum(value_2) over (partition by id) "sum"
from the_table;

    DB-fiddle

    Login or Signup to reply.
  2. 0

    I don’t quite follow why your sum and other columns should be the way they are (such as val1 is same for all), but this solution returns your desired output:

    create table t1 (
  id integer, 
  unit varchar(1), 
  sub_unit integer, 
  value_1 integer, 
  value_2 integer
  );

insert into t1 values 
(1, 'A', 1, NULL, 10),
(1, 'A', 2, NULL, 10),
(1, 'A', 3, NULL, 10),
(1, 'A', 4, NULL, 10),
(1, 'B', 1, 5, NULL),
(1, 'B', 2, 5, NULL),
(1, 'B', 3, NULL, NULL),
(1, 'B', 4, NULL, NULL);

    select 
 id, 
 unit, 
 sub_unit, 
 max(value_1) over (partition by id) as value_1, 
 max(value_2) over (partition by id) as value_2, 
 max(value_1) over (partition by id) *
 max(value_2) over (partition by id) as sum_col
from t1;
    id unit sub_unit value_1 value_2 sum_col
    1 A 1 5 10 50
    1 A 2 5 10 50
    1 A 3 5 10 50
    1 A 4 5 10 50
    1 B 1 5 10 50
    1 B 2 5 10 50
    1 B 3 5 10 50
    1 B 4 5 10 50

    View on DB Fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search