Select() of function - Calculate a value and separated by one of column

[Sport_MACMUS]

Hello everyone,

I’d like to calculate a value of arrived-quantity from the order table, which are 5 columns, OrderID, Item, SKU, OrderQuantity, and ArrivedQuantity.

I’ve used select() of functions, something like this:

sum(
SELECT(
order[arrived-quantity],([SKU] = [_ThisRow].[SKU])
)
)

It’s worked. But there was something wrong when I added more than 2 records.

i.g. I ordered 5 apples with OrderID column of ABC and I received 5 apples in the first time, OrderQuantity is 5 and ArrivedQuantity is 5, the select() of functions solves the problem perfectly.

When I added the second record, the problem has happened. I ordered the same item and quantity with OrderID column of DEF, OrderQuantity is 5, but ArrivedQuantity is coming to 10.

I tried editing the select() to like this:

sum(
SELECT(
order[arrived-quantity],
AND(
([SKU] = [_ThisRow].[SKU]),
([OrderID] = [_ThisRow].[OrderID])
)
)
)

but it doesn’t work anyway.

I want my ArrivedQuantity’s result can be separated by OrderID. Anybody can help me to do that? I will appreciate it.

[WillowMobileSys]

The SUM(SELECT()) do not work in the way you are thinking. Your SELECT() simply returns a list of “arrived_quantity” values. SUM() simply adds all of the values together.

You will not be able to build an expression to return a group of summed values. You’ll need to find another way to accomplish your need.

Please describe in more detail your app, what your are trying to do and where in the app you are trying to do. Then the community can help better to come up with a solution with you.