How to get the sum of a column in a table component

App Building
,
1

I have a column of integer type and simply want to get the sum of all the values in the column. Is there a really simple way to do this?

2

How about this?

image
image1920×1080 115 KB

Here is the default table and a number component. In the number component I have the Default value as

{{table1.data.unit_price_cents.reduce((x,y)=>x+y,0)}}

This will sum the column of the table data!

Hope this helps.

Jeremy

1 Like
3

@jclutterbuck

Thank you that's very helpful. I think that would normally work but the column I am trying to add is a custom made column. Is this possible?

4

At the moment custom columns can be thought of as being display-only. Depending on how you're using the custom column you might, instead, add the column to your table data via a transformer before passing it to the component itself, that way you can access it as part of yourTable.data. Otherwise, you can copy the column logic into the aggregator.

Let's say the value of your custom column was something like {{ currentRow.unit_price * currentRow.units_sold }}. To add it as a regular column via a transformer you could do something like:

{{ formatDataAsArray(table1.data).map(row => ({...row, total_sold: row.unit_price * row.units_sold})) }}

If you were to use the reducer you could try:

{{ table1.data.unit_price.reduce((x, y, index) => x + y * table1.data.units_sold[i], 0) }}

Let me know if that works!

1 Like
5

Thanks for this tip.

How would this apply if we filter the table results?

Thanks,
Tom

6

@Kabirdas

I'm struggling with this a bit, mostly because I'm rather new to Retool and the more complex scripting tools, but I also need some help with this.

I also have a custom column, but the one I've set up calculates the total hours from a clock in and clock out period. I'd like to calculate the total hours the 7 day period, but can't get either the reducer method or transformer to work.

The custom column works off this: {{(currentRow.finish - currentRow.start) / 3600 }}, so I've tried the transformer method of creating a new column:

return
{{ formatDataAsArray(Table_Advanced_Selection.data).map(row => ({...row, total_hours: (row.finish - row.start) / 3600 })) }}

and the reducer method, but with no luck.

Any assistance or advice would be very helpful.

7

Hey @JMWRetool!

How is Table_Advanced_Selection.data structured? One of the things I didn't specify in my post is that table data typically comes in two formats - either an array of objects (e.g. [{foo: "bar"}, {foo: "baz"}]) or an object of arrays (e.g. {foo: ["bar", "baz"]}). The formatDataAsArray and formatDataAsObject functions are to convert between the two. If you use formatDataAsArray on data that's already an array it can cause issues, in that case you'd want to use:

{{ Table_Advanced_Selection.data.map(row => ({...row, total_hours: (row.finish - row.start) / 3600 })) }}

and if you wanted to use an aggregator it might look something like

{{ formatDataAsObject(table1.data).unit_price.reduce((x, y, index) => x + y * table1.data.units_sold[i], 0) }}
8

hi all,
is there any new way to calculate a sum of (new table component) column values (numbers)?
Can I calculate only the displayed values?

tried this

let sum = 0;
for (let i = 0; i < data.length; i++) {
sum += parseInt(data[i].column_name);
}

and then use {{ sum }} in the display input.
doesnt find {{sum}} :frowning:
I would like to display it in a statistic component.

9

Hey @Ana! Typically variables you declare in a script or JS query won't be accessible outside of that context. There are a few different approaches you can use here though:

The lodash _.sumBy function can be super helpful here. You can try something like {{ _.sumBy(yourTable.data, row => parseInt(row.column_name)) }} if your data is already in numerical form you can also do {{ _.sumBy(yourTable.data, 'column_name') }}.

If your data is coming from a SQL query it should already be formatted as an object of arrays, in which case you can use {{ _.sum(yourTable.data.column_name.map(parseInt) }} or {{ _.sum(yourTable.data.column_name) }}.

You mentioned only wanting to sum over the displayed values though - would you mind clarifying a bit what you mean by this? Are you using filters? Mapped values? Would you like to include edits to the table?

10

hi! I just wanted to display a sum of all variables type number from one table column in separate statistic input.

I've done it the following way and it works. However works for all data in a column (doesnt work when I filter rows :frowning: )

{{ formatDataAsObject(table1.data)['column_name'].reduce((x, y) => Number(x) + Number(y), 0) }}

this is what comes into statictic component value.

11

so how to use the variable calculated by a js query in inputs for example?

I have a random id number generated by a js function in js query.
I would like it to appear in an input in a form :slight_smile:

12

I see, unfortunately there isn't a great way to access just filtered data from the table component at the moment :confused: The best that I know to recommend is either adding your own custom filter to your JS query or using a Query JSON with SQL query to filter the table data.

If you want to access a variable from your JS query you'll just need to return it! E.g.:

let sum = 0;
for (let i = 0; i < data.length; i++) {
sum += parseInt(data[i].column_name);
}
return sum;

Then you can access that value using {{ yourJSQuery.data }}. If there are multiple variable you want to declare and access you can try something like:

const foo = "foo";
const bar = "bar";
const baz = "baz";

return {foo, bar, baz};

In which case the query will return the full object and you can access {{ yourJSQuery.data.foo }} etc.

Let me know if that helps!

13