Alternative approach to correlated sub queries

Alternative approach to correlated sub queries

We are using Zoho Analytics with data from Xero, we wish to show the bank account balances per day, charted over 30 days.

To do this we need the sum of all account transactions to date grouped by account and day. How can I achieve this?
I would normally approach this in SQL with a correlated sub query, something like this:
  1. SELECT 
  2. a."Account ID",
  3.     a."Bank AccountType",
  4.     a."BankAccountNumber",
  5.     a."Name",
  6.     j."Journal Date",
  7.     j."Converted Net Amount",
  8. (SELECT SUM(JournalsInner."Converted Net Amount") FROM "Journals" as JournalsInner WHERE JournalsInner."Account ID" = a."Account ID" AND JournalsInner."Journal Date" <= j."Journal Date") as BalanceToDate 
  9. FROM 
  10. "Accounts" a
  11.     INNER JOIN "Journals" j ON a."Account ID" = j."Account ID"

I understand that correlated sub queries won't work for query tables so how can I achieve this?

Many thanks

Simon

            Zoho Marketing Automation

                    Zoho Pagesense Resources

                      Zoho SalesIQ Resources



                            Zoho TeamInbox Resources

                              Zoho DataPrep Resources



                                Zoho CRM Plus Resources

                                  Zoho Books Resources


                                    Zoho Subscriptions Resources

                                      Zoho Desk Resources

                                        Zoho Projects Resources


                                          Zoho Sprints Resources


                                            Qntrl Resources


                                              Zoho Creator Resources


                                                Zoho WorkDrive Resources





                                                    Zoho Campaigns Resources

                                                      Zoho CRM Resources

                                                            Design. Discuss. Deliver.

                                                            Create visually engaging stories with Zoho Show.

                                                            Get Started Now