Sum with IF - please help

[wanass123]

I have below data : 

If customer paid extra we should count only the required amount as Final payment 

for example customer bill is : 50 and he paid 150 we consider only 50 as Final payment 

below formula work fine in the table : 

if((Sum(Payment_Amount)-Account_Balance) >= 0 , Account_Balance , sum(Payment_Amount))

Company	Account_Balance	Payment_Amount	Final_Payment
Company 1	10	8	8
Company 1	12	14	12
Company 1	3	3	3
Company 1	16	20	16
Company 1	20	20	20
Company 1	33	35	33
Company 1	100	500	100

now I need to create a small table with the total of final amount example : 

Company	Total Final_Payment
Company 1	192

I try to add in the load script below formula : 

if((Sum(Payment_Amount)-Account_Balance) >= 0 , Account_Balance , Payment_Amount) as  Final_payment,

unfortunately it doesn't  work  gives error :  

The following error occurred:

Invalid expression

[BrunPierre · Accepted solution]

When using aggregation functions like sum in the script, you would also have to apply the Group by clause appropriately.

How about without the sum;

IF(Payment_Amount-Account_Balance >= 0 , Account_Balance , Payment_Amount) as Final_payment

or with the sum and Group by clause as below

DataTable:

LOAD Company,

            Account_Balance,

            Payment_Amount

FROM SourceDate;

Left Join(DataTable)

LOAD Company,

Sum(IF(Payment_Amount-Account_Balance >= 0,Account_Balance,Payment_Amount )) as Final_payment

Resident DataTable

Group by Company;