1

I have a Payments object with dates and corresponding payments. We can query the data via SOQL in our API and the data looks like this:

SELECT Date, Amount FROM Payments WHERE CreatedDate>2019-01-01 AND Account=0011K00009994H5
~
Date        | Amount
------------+--------
2019-01-01  |  30.00
2019-01-02  |  20.00
2019-01-03  |  30.00
2019-01-04  |  20.00
2019-01-05  |  30.00
2019-01-06  |  20.00

We would like to run a single SQL query shows a running total of payments by date looks like this:

Date        | Amount | Total
------------+--------+--------
2019-01-01  |  30.00 |  30.00
2019-01-02  |  20.00 |  50.00
2019-01-03  |  30.00 |  80.00
2019-01-04  |  20.00 | 100.00
2019-01-05  |  30.00 | 130.00
2019-01-06  |  20.00 | 150.00

I know how to do this in SQL Server or MYSQL but is this possible using SOQL in one query?

PS: We are currently doing this manually using multiple queries and we are constantly running out of API requests.

2

Unfortunately, SOQL does not have functionality like this. You can find the overall SUM() of a field but not in a running total.

Here is a link to the available SOQL Aggregate Functions: https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_agg_functions.htm

I believe to achieve this in Salesforce, you'd need to combine this with Apex and hold the running total in a variable you can then print to Visualforce Page or Aura/Lightning Component.

1
  • Thanks - I'd like to keep the question open for a while before accepting this answer. I'm hoping someone can add a workaround that can be called via the SOQL API endpoint. Jan 15 '20 at 3:21

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.