How to find the sum of certain column values ​by conditions from another one?

[auto_724]

There are 2 columns. 1st one with values in TEXT format - SKU, 2nd one with integer values. How to create expression to sum qty from the second column according to the conditions of matching the specific SKU from the first? For ex Im interesting in sum of the values ​​that match SKU/3 which should be 26? Should I use SUM or COUNT expression. What conditions should I used?

Thanks

[dbaum]

The SUM function's argument is a list. So, you need an expression that returns the list of values you want to sum; then use that expression as the argument for the SUM function.

There are more and less efficient ways to construct that expression depending on the context where you're creating it. For example, if the table you screenshotted is an Orders table and you also have a master SKU table that serves as the parent for the Orders table, then the SKU table likely already has a column named something like Related Orders, which is a list of Orders IDs that you can then dereference to return a list of quantity values for each row's SKU:

SUM([Related Orders][Qty])

[auto_724]

Screenshot shows Products table, Order table is a different one. Key of those two tables is a parameter "part number" and SKU is an additional value.

Also I added new table SKU which represents purchased SKUs and key in this tabel is SKU value.

What I want to do is generate this list from product table and use is in SKU tabel as a Ref.

[bowker6789]

You could try the following:

SUM(SELECT(Products[Qty], [SKU] = [_THISROW].[SKU]))

[auto_724]

it says that expression is valid:

SUM( ........The list of values of column 'Available' ........from rows of table 'Products' ........where this condition is true: ((The value of column 'SKU') is equal to (The value of 'SKU' from the row referenced by 'SKU')))

but where I should put specific SKU that need to be added up?

[bowker6789]

Do you plan on putting the SKU into another form or separate table and have it calculate the total for you? If so you will need to put a link (Ref) between the Products Table (above) and the new table where you plan to select the SKU.

If this is the case you need to make sure that the SKU is set as the Key in the products table, if this is not possible set it as the label and see if that works. Make sure you change the column type of the SKU column in the new table to REF and point it to the Products table.

Then in the new table create a virtual column with the formula above.

[auto_724]

I can create new table TEST. From what I know TEST table and Product table are already linked automatically as a source.

I cannot mark SKU as a KEY in a Product table but there is different value there which I need.

---

[bowker6789]

Can you mark SKU in the Products table as a label?

Then when you select the SKU in the Test table (form view) it should automatically calculate the number of items that match the SKU.

[bowker6789]

Add another virtual column to the TEST Table, call it "SKU Name" and use the formula:

[SKU].[SKU]

Then in the column you want to make the addition, lets call this SKU total use the following formula, please substitute [Unique ID] for whatever the key column is in the Products table.

SUM(SELECT(Products[Qty], [Unique ID].[SKU] = [_THISROW].[SKU Name]))

[auto_724]

---

@bowker6789 wrote:

Can you mark SKU in the Products table as a label?

---

OK done

@bowker6789 wrote:

Then when you select the SKU in the Test table (form view) it should automatically calculate the number of items that match the SKU.

OK done and this is what I was looking for. I have "table" view of all available SKUs:

@bowker6789 wrote:

Add another virtual column to the TEST Table, call it "SKU Name" and use the formula:

[SKU].[SKU]

It works like that: The value of 'SKU' from the row referenced by 'SKU'

---

@bowker6789 wrote:

Then in the column you want to make the addition, lets call this SKU total use the following formula, please substitute [Unique ID] for whatever the key column is in the Products table.

SUM(SELECT(Products[Qty], [Unique ID].[SKU] = [_THISROW].[SKU Name]))

---

SUM( ........The list of values of column 'Available' ........from rows of table 'Products' ........where this condition is true: ((The value of 'SKU' from the row referenced by 'Numer') is equal to (The value of 'SKU Name' from the row referenced by 'SKU')))

[auto_724]

Basically all I need is to create VC in TEST table which will make reference to column from a different table (table of "all purchased SKU") and return all values into TEST table. After that I will compare both columns.

I was trying to add new VC [purchased SKU] to this table that I created yesterday [TEST] with this expression:

SUM(SELECT(Table with all added SKU[all purchased SKUs column], [SKU].[SKU] = [_THISROW].[purchased SKU]))

Where "Table with all added SKU" is a table with all bought SKU with Key-SKU and Label-SKU.

SUM( ........The list of values of column 'all purchased SKUs column' ........from rows of table 'Table with all added SKU' ........where this condition is true: ((The value of 'SKU' from the row referenced by 'SKU') is equal to (The value of 'purchased SKU' from the row referenced by 'SKU')))

This is how it looks:

All data I have received is "0" for each SKU

[auto_724]

Another thing.

I created extra table which is in form view and this is a "purchase form" of specific SKUs. 

For example I bought 50 pcs of SKU "CY/3".

I have another table in table view which shows me all added (bought) SKUs.

What I want to do is change (reduce) value of added (bought SKU) in this table by the SKUs that are currently available (thing what we did earlier).

can you advise how to do it in the same table?

I was trying to add new VC [purchased SKU] to [TEST] table with this expression:

SUM(SELECT(Table with all added SKU[all purchased SKUs column], [SKU].[SKU] = [_THISROW].[purchased SKU]))

Where "Table with all added SKU" is a table with all bought SKU with Key-SKU and Label-SKU.

SUM( ........The list of values of column 'all purchased SKUs column' ........from rows of table 'Table with all added SKU' ........where this condition is true: ((The value of 'SKU' from the row referenced by 'SKU') is equal to (The value of 'purchased SKU' from the row referenced by 'SKU')))

All data I have received is "0" for each SKU