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


            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