How to do sql operation such as sum on a child table before it is inserted to the database

need to sum quantity in the child table where itemcode and warehouse is same but the problem is that the child table is not in database because its not saved yet i have to write this function in before_insert

It’s better that it’s not in database yet, you can modify it even before it hits the database. You could just inspect the child table objects that are about to be inserted.

A silly prototype below will help, you’ll need to consider many edge cases and other fields too, so be aware of that!

# before insert / validate hook server script

item_wh_qty = defaultdict(int)

for item in doc.items:
    item_wh_qty[(item.item_code, item.warehouse)] += item.qty

# now use combined items to recreate `doc.items`

I’d not recommend this customization though, do you really need to merge the same items? Why not just use “Group similar items” in the document’s print setting. That will consolidate items while printing but show the same table in ERPNext.

Also you can stop users from adding the same items multiple times from selling/buying settings.