How to use aggregation in the script level - Backend

[#1]

hi

I need aggregate the value with respect to each invoice , i have the values in terms of row level data. Kindly suggest how can i implement it in the script part

Thanks

Sathis

[Peter_Cammaert · Accepted solution]

The GROUP BY clause is your friend here. It may take you some time to figure out exactly how it works, though. An example may help. Assuming you have Orders and OrderLines tables and you want to add the Order Amount to your Orders table:

LEFT JOIN (Orders)

LOAD OrderID,                 // This will be the JOIN field

     Sum(OrderLineAmount)     // Sum the Amounts for all OrderLines of every Order

RESIDENT OrderLines

GROUP BY OrderID;

[Anonymous]

you need to use group by

select invoice,

          sum(value)

from dbtable

group by invoice

[#4]

i understood how to use the group by Clause

Thank You

New Group by Clause Leaners :

with the above example

LEFT JOIN (Orders)

LOAD OrderID,                 // This will be the JOIN field

     Customer_Name,           // Either Comment or include it in the group by clause

     Sum(OrderLineAmount)     // Sum the Amounts for all OrderLines of every Order

RESIDENT OrderLines

GROUP BY OrderID;

If in case a ROW ID column is available in the data pls dont include it in the Group by clause because Row ID is a Distinct Column which does not support Group by Clause.

Thanks

[Peter_Cammaert]

If it works for you, please mark a discussion answer as Correct. The blue balloon next to your question in the community overview will turn green. That way, other community visitors that are searching for answers will more easily find and visit your discussion. Thanks.

.