Sum of items in a query


So, first of all, I'm using google sheets as a resource.
I have a sheet with people, let's name it People, then I have another sheet with the column name and the column action, lets name it Actions.
I have 2 tables in retool whith 2 queries to get all the info from both sheets to each table
I'd like to create a new column in the People table with the sum of the Actions.

Table Actions
Mark | Action1
Mark | Action1
Mark | Action2
John | Action2

So the table should be:
Table People
Name | Number of actions 1 | Number of actions 2
Mark | 2 | 1
John | 0 | 1

How can I make those calculations to the new columns?


Hello, you can do the following

  1. Create a custom column
  2. In mapped value use the following script referencing your Actions table and the ID´s that correspond

(This for each existing Action)

{{, val) => {
    if (val.action_id === 1 && val.user_id === { // if action_id is equal 1 (Action 1) and user_id is equal the user id of the current row, add 1
      return acc += 1
    return acc
  }, 0)


Thanks! I'll check it!

Works fantastic, thanks again.

1 Like