Trying to sum values from Query JSON with SQL query

[sfbrophy]

I'm trying to sum the values returned from a Query JSON with SQL query, but getting only zero as the result. Here's my code:
select sum(availableRenewal) from {{query_AllOpptys.data.map(i => parseInt(i.availableRenewal))}}

In the watcher I can see that the JSON object is populated with the right data in the right type (ints not strings). Any ideas? Thanks.

[justin]

Hey @sfbrophy, good to hear from you again

We should be able to get this figured out. Can you send over a screenshot of the values of the relevant properties in the property inspector?

[justin]

We were able to get this sorted out by moving the data type conversion around:

select SUM(CAST(availableRenewal AS NUMBER)) 
from {{ query_AllOpptys.data }}

[rolandcrepeau]

Hello here,
I think I have a similar issue on my retool.
Here is the values I would like to sum.

shootingItems is a Postgresql query.

I would be very happy to have an answer from you,
Thanks,

[rolandcrepeau]

I found a solution using something like this #lodash
{{_.chunk(query_name.data)}}
Hope this will help someone

[SalmanMaqbool]

Hi Justin,
How to get a sum of column values to show in the statistics component. I have a table column name "Inc Fee" in which string values like "$1500". I need to show the sum of all of that column. Please help me get out of it.

[justin]

Hey @SalmanMaqbool! You'll need to:

1. Remove the $ from the string
2. Then convert it to a number

The syntax depends on what database you're using.

[SalmanMaqbool]

I am importing data from google sheets. If I remove $ sign and get data in numbers then how to sum all the fields of a column? Any casting require from string to number or maping to sum all column values?

[justin]

@SalmanMaqbool could you share more details on what the column looks like / a screenshot? If you're using Query JSON with SQL then a simple SUM(column_name should do it.

[SalmanMaqbool]

See this image

. I have data from google sheet not from json or SQL query. Its simple google sheet data with some columns having number and I want to get sum of a specific column. Look I have tried {{_.sum(table1.data['7'])}}. But is saying that is requires string and you are providing numbers. ['7'] presents the column "Expected views".