CODEWITHSUNDEEP
hivehiveqlimpala
A Saraf
A Saraf

Reputation: 275

Impala/Hive - Sum up the amount for Adjustment code

I have an Impala table named REV having wire_code and amount for each wire code

+---------+------+
|wire_code| amt  |
+---------+------+
| abc     | 100  |
+---------+------+
| def     | 50   | 
+---------+------+
| ghi     | 250  |
+---------+------+
| jkl     | 300  |
+---------+------+

and the other table is FA which is having wire_code and Ajusted_wire_code.

+---------+------+
|wire_code|adj_wc|
+---------+------+
| abc     | def  |
+---------+------+
|  ghi    | jkl  |
+---------+------+

I need to adjust the amount of wire code which is available as adj_wc in FA table. For example:

"abc" is there in FA table and its getting adjusted to "def" then my output should be - wire_code "def" is having amount of (abc+def) as below. "abc" amount will remain same.

+---------+------+
|wire_code| amt  | 
+---------+------+
| abc     | 100  |  
+---------+------+
| def     | 150  |
+---------+------+
| ghi     | 250  |
+---------+------+
| jkl     | 550  |
+---------+------+

Please help in this query. Thanks in Advance!

Upvotes: 1

Views: 92

Answers (1)

leftjoin
leftjoin

Reputation: 38325

Using two joins:

select r.wire_code, r.amt+coalesce(a.amt,0) as amt
  from REV r
       left outer join FA f on r.wire_code=f.adj_wc     --adjustments
       left outer join REV a on f.wire_code=a.wire_code --adjusted amount
;

Upvotes: 2

Related Questions