1

I need to sum up the values of a given field rather than counting . The code creates the variable 'sum' that gives you the number of rows, what if need the sum of the values of a given field/column?? is there any sum() operator I can include to get it?

//sql API to filter and then js to count the number of rows filtered

sql.execute("SELECT * FROM table_4mi_dataset_p5 where type in ("+ incident + ") and origin in ("+ origin + ") and sex in ("+ gender + ")") 
.done(function(data){
 for(i = 0; i < data.total_rows; i++){ // loop that reads each column of the table
    sum = data.total_rows}

console.log(sum)


}); 
0

You can do this by creating two variables: sum and val.

Thos variables will be set as global variables in this way:

var sum = 0;
var val;

Then, when you call the CARTO SQL API with the sql.execute(query...), you can do something like:

sql.execute("SELECT homes as homes FROM barris_barcelona_test") 
              .done(function(data){
                for(i = 0; i < data.total_rows; i++){ // loop that reads each column of the table
                  val = data.rows[i].homes
                  sum = sum + val

Where val = data.rows[i].homes is taking the value of the column named homes from the dataset and storing it in the varibale val (for each iteration, so the variable val will have the same value as the value of the column homes in a specific row)

Then we do a "manual" sum operation with sum = sum + val so it will sum the value of the previous sum variable with the new value of the variable val. So at the end of the iteration, the sum variable will have the sum of all the rows of the column as a result.

Here you can find an example

1
  • Sorted and working perfectly. However as Jorge Sanz say below , it might be more simple to just use SUM(myfieldname) as I only need 1-filed total value. If you have any idea about how to return the sum value from the query in the SUM variable let me know. Dec 21 '16 at 14:03
0

Why retrieving the full dataset when you can simply do the math on the database? Just use SUM(myfieldname) on the SQL statement instead of the * and CARTO will return you the computation. Same goes to the counting with COUNT(*).

If you still need the individual values for any other stuff, I recommend only retrieving the fields you need instead of everything, you'll notice the speed up 😊

3
  • yeh, I don't need individual values at all, I just need a the resulting sum( ) of a specific field , then I need to create a variable (how??) of that figure , which will be displayed into a </div> element when the query is applied . the sql, as you said, would be Something like this--->> sql.execute("SELECT SUM(incident) FROM table_4mi_dataset_p5 where type in ("+ incident + ") and origin in ("+ origin + ") and sex in ("+ gender + ")") }); I just wonder how to create the variable after the sql query??? Dec 21 '16 at 10:37
  • Exactly the same way you are handling it already from the data returned by the callback (data). Just put a breaking point on the callback and inspect the data object. It's plain easy but still, check the documentation and this example that does exactly what you want.
    – Jorge Sanz
    Dec 22 '16 at 8:54
  • Also mark the question as solved if you don't mind :)
    – Jorge Sanz
    Dec 22 '16 at 8:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.