How to combine a sum value from one query with another query

Wappler General How To
#1

Hey all,

For some reason struggling with this one.

This is the scenario:
I need to sum the value of a field in a Multiple Records query (query1)
I need to sum the value of a field in a Multiple Records query (query2)
I then want to add both of those values into one value. So query1.sum(field1 * field2) + query2.sum(field1 * field2)

Both queries query the same table. But, I have another field I’ve been trying to use a where condition of either 1 or 2 to get the different results for query1 and query2, which isn’t working.

Seems simple enough, but I just can’t get it to work.

I’m ending up having to create a query for each condition (1 or 2). Then repeat on that query with a setValue to create the output per row. On the front end I then sum the array on that setValue output.

But I still can’t combine.

I’m certain this should be simple. Perhaps this is one of those requirements of MySql views? I’m just not familiar and from what I understand these still are not supported.

Any help appreciated.

#2

Hi. You want to sum or multiply?
Using sum is straightforward with a property/column name. I think we had a discussion about it sometime ago.
Using sum on a product of two fields, should also be possible, but havent tried yet.

Can you share sample data of the two queries and desired output? I could not follow the rest of the text.

#3

Hey Sid, we may have - but I don’t remember :sweat_smile:

To simplify:

DB Table = jobs

Fields to use: job_type, fee_per_position, total_positions

I’m trying to achieve the following stats:

Total fees for job_type ‘1’
Total fees for job type ‘2’
Total positions for all job types
Total fees for all positions and all job types.

Where it is a litle challenging is that one row might have:

job_type: 1
fee_per_position: 5000
total_positions: 5

and another row might have:

job_type: 2
fee_per_position: 15
total_positions: 1

So you can’t simply get all positions * all fees - as it needs to be calculated per row then sum.

#4

So you make use of group by job type and then do the calculation.
I am replying on mobile so you will have to experiment a bit on server or client side to make that work. But group by should get you the results.

1 Like
#5

Thanks sid, I forgot about group by - let me try that, appreciate the guidance.