Help With Extracting a SUM from the database

Server Side Components
#1

I want to use the sum of some values from the database.

I have this query called booking_allocations:

sum100
sum1001023×541 75.7 KB

Which I assumed would return the sum of the fields found. However it is only returning single field value:

sum101
sum101778×211 35.5 KB

What do I need to do in order to return the sum, so for example to return
amount_allocated_local_sum = 360.00…?

And how do I then access that value in a Set Value?

1 Like
#2

What values do you want to sum exactly?
All the rows values in a column from your database table, like:

Screenshot_32
Screenshot_32902×658 262 KB

#3

Yes!

So in my example, 210+150=360…

#4

Adding one column with the SUM Aggregate Function added will return the total sum of all the rows values:

Screenshot_33
Screenshot_331073×633 96.1 KB

Screenshot_34

#5

Thanks for that Teodor…

So am I not getting a sum of all the rows returned because I also asked to return allocations.id not as a sum?

Do all columns in the query need to be SUM aggregate for it to work?

I guess in that case I need to use .sum() in my code to get the sum…

#6

Umm not sure i understand your question.
If you want to add a sum of a column in your database table, then you add it in the query builder and apply the aggregate function SUM to it.

#7

Yes, but if you look at my example, I’ve done that and I’m not getting the sum returned and I’m trying to work out why… hence my question related to my example that isn’t working.

#8

Well you added an ID column there, which is used as a GROUP BY for your query, which means that all the uniquer ids will be listed on a new row …

#9

Okay, that makes sense now. I do need the individual id values, so I need to do a .sum() in the code.

So many subtleties to learn…

Thanks for your help! :slight_smile:

#10

Teodor, I’m not finding it easy to work out how to do the sum() in the code.

So in your example…

if your query was not an aggregate, how would you assign like this?

Set Value quantity_all = [sum of all products.quantityInStock as a number]
SO YOU CAN THEN SAY:
Set Value quantity_all_plus_one = {{quantity_all + 1}}

Thanks!
Antony.

#11 
{{query1.sum("quantityInStock")}}

Returns the Sum of the quantityInStock column returned by your query1.

{{query1.sum("quantityInStock") + 1}}

Returns the Sum of the quantityInStock column returned by your query1 + 1.

1 Like