Group by functionality in table

Hello everyone!

I have a table with two columns: created_at and impression_count

Very simply, I'd like to group the data by created_at and take the sum of impression_count for each group.

After that, I'll chart the time-series data with the newly aggregated data.

All would be quite simple in Python/Pandas (my native tongue), but I'm having trouble finding grouping functionality/writing the proper JS in Retool.

Any and all help is greatly appreciated!

This really should be done in the query using group by and/or sum so if you can post your query here it might be more helpful for me to edit it and determine how it should be written

@ScottR agreed! So I'm originally pulling from a rest API and using a JS query to loop through each page of the API results:

All is well there.

I then pull the results of the JS query into the table below:

The JSON for the JS query looks like this, so I had to use some custom columns in the table to get things formatted properly:

All this said, it looks like I could either 1) write a transformer for the original JS query so that the results are grouped by created_at or 2) create a 'Query JSON with SQL' resource to modify the result of the original JS query.

Hopefully this is right! I'm a bit of a Retool noob.

This would be the way to go....

Also, this post may put you in the right direction:

@ScottR Thank you for this! Reading through this post now. I'm facing a similar issue with accessing all indices inside a query:

SELECT {{[i].created_at }}, SUM({{['public_metrics'][i]['like_count'] }})
FROM {{ }}
GROUP BY {{[i].created_at }}

I'm sure there are cleaner ways right to this by assigning variables -- Where i am going wrong?

@jordanp What is the error, if any, that you are seeing or what is the result?

@ScottR it looks like created_at isn't getting retrieved properly, so like_count doesn't aggregate correctly either. i assume it has something to do with how i use index i.

@jordanp Hard to say what it could be with not being able to see your code.... I would type out one element at a time where your seeing that error and see what Retool suggests as you add a . after each element... it may help you get to the correct structure you need within the SUM....

@ScottR i figured it out!

i was overcomplicating the syntax. looks like Retool SQL automatically recognizes keys in a JSON when you specify the resource in the FROM statement.

for accessing nested JSON keys, you can use AlaSQL's -> instead of .

hope this helps others in the Retool community!

1 Like