Impala - Calculate Percentage From One Table

Question

I have this kind of table, named it as table_A

 Cust        Amount      Src_cust
  A           2000         B
  A           3000         C
  A           1000         B
  C           1000         B

Result

 Cust        Percentage  Src_cust
  A           50 %         B
  A           50 %         C
  C           100%         B

I want to get this kind of data, i'm using impala for my query but it failed to get the percentage.

Select Cust,(Sum(Amount) * 100) /sum(sum(Amount)) over() as  percentage, Src_cust
from table_A

This code give me result:

Cust        Percentage  Src_cust
  A           0            B
  A           0            C
  C           0            B

Help please. Thank You

score 1 · Accepted Answer · answered Dec 04 '18 at 03:20

1

You need some aggregation in your attempt. I think the right query is:

select cust,
       sum(amount) * 100.0 /sum(sum(amount)) over (partition by cust) as  percentage, Src_cust
from table_A
group by cust, src_cust

answered Dec 04 '18 at 03:20

Gordon Linoff

1 Answers1