CODEWITHSUNDEEP
sqlleft-join
kanes
kanes

Reputation: 79

SQL, left join table, How to keep only one value?

I would like to join two tables. The table 1 is like this:

id      Date
a     01.01.2021
a     02.01.2021
a     03.01.2021
b     01.01.2021
b     02.01.2021
b     03.01.2021
c     01.01.2021
c     02.01.2021
c     03.01.2021

The table 2 is like this:

id  value 
a    12
a     8
b    50

As final result, I would like to have a table like this: for an existing id from table 2, the sum of the value of the same id should be stored in the last date in the table 1.

   id       Date     Value_FINAL
   a     01.01.2021    0
   a     02.01.2021    0
   a     03.01.2021    20
   b     01.01.2021    0
   b     02.01.2021    0
   b     03.01.2021    50
   c     01.01.2021    0
   c     02.01.2021    0
   c     03.01.2021    0

I tried to use left join to join these two tables at first,

with t3 as ( select id, sum(value) Value_FINAL from t2 group by id) 
            select t1.*, t3.value_FINAL from t1 left join t3 on t1.id = t3.id;

After this, I can get this:

   id       Date     Value_FINAL
   a     01.01.2021    20
   a     02.01.2021    20
   a     03.01.2021    20
   b     01.01.2021    50
   b     02.01.2021    50
   b     03.01.2021    50
   c     01.01.2021    0
   c     02.01.2021    0
   c     03.01.2021    0

But, it is not I want. can someone help with this? How can I keep the value only in the last Date in the column 'value_FINAL' I am also thinking about to use last_value(value) over (partition by id order by date). But I need to create an extra table or column.
maybe someone has a good idea how to deal this problem?

Upvotes: 3

Views: 1696

Answers (3)

Rajat
Rajat

Reputation: 5803

A join based alternative

 select a.*, coalesce(c.value,0)
 from t1 a
 left join (select id, max(date) date from t1 group by id) b on a.id = b.id and a.date = b.date
 left join (select id, sum(value) value  from t2 group by id) c on b.id = c.id

Upvotes: 3

The Impaler
The Impaler

Reputation: 48770

You can use ROW_NUMBER to identify the row where you want to place the total value.

For example:

select
  t1.id, t1.date, b.total,
  case when
    row_number() over(partition by t1.id order by t1.date desc) = 1
  then b.total
  else 0 end as value_final
from t1
left join (select id, sum(value) as total from t2 group by id) b 
  on b.id = t1.id

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269643

Use row_number(). One method is:

select t1.*,
       (case when row_number() over (partition by id order by date desc) = 1
             then (select coalesce(sum(t2.value), 0) from table2 t2 where t2.id = t1.id)
             else 0
        end) as value_final
from table1 t1;

Upvotes: 1

Related Questions