hive query for derived columns

Question

Could you please guide.

Input table (Table1) have columns like credit_date, credit_amount, debit_date, debit_amount, loan_date, loan_amount.

Output table(Table 2) have columns like date, credit_payment, Debit_payment, Loan_payment.

Date : should combine all values of credit_date, debit_date and loan_date.

Credit_payment: Find the sum of credit amount for a given credit_date.

Debit_payment: Find the sum of debit amount for a given debit_date.

Loan_payment: Find the sum of loan amount for a given loan_date

I tried below query but not working.

insert into table2 
select 
date,
debit_payment,
credit_payment,
Loan_payment
 from (
select 
sum(credit_amount) over parttion by credit_date as credit_payment,
sum(debit_amount) over parttion by debit_date as Debit_payment
sum(loan_amount) over parttion by loan_date as Loan_payment
from table1
union all
select credit_date as date from table1
union all
select debit_date as date from table1
union all
select payment_date as date from table1
) t

------------------------------------------------------------------------

I have another scenario where credit_Date, debit_date and loan_date can be same. Output table have below columns

Date: should combine credit_date, debit_date and loan_date ( credit_date, debit_date and loan_date can be same or different also)

**Credit_payment:**Find the sum of credit amount for a given credit_date, entity, currency, owner

Debit_payment: Find the sum of debit amount for a given debit_date, entity, currency, owner

Loan_payment: Find the sum of loan amount for a given loan_date, entity, currency, owner,

entity: values from Table1

currency : values from Table 1

Owner: values from Table 1

Total : sum of ( credit_payment + debit_payement+ loan_payment)

could you please guide.

Please find the screenshot as below.

Answer 1

You might need to explicitly specify the null columns before you do union all:

insert into table2 
select *
from (
    select credit_date as date, sum(credit_amount) as credit_payment, null as debit_payment, null as loan_payment
    from table1
    group by credit_date
    union all
    select debit_date as date, null as credit_payment, sum(debit_amount) as debit_payment, null as loan_payment
    from table1
    group by debit_date 
    union all
    select loan_date as date, null as credit_payment, null as debit_payment, sum(loan_amount) as loan_payment
    from table1
    group by loan_date
) t
order by date;