title: Django cumulative sum in model (sqlite)
body: <p>My model is:</p>
  <pre><code>class transaction(models.Model):
      transactiondate = models.DateField()
      category = models.ForeignKey(category, on_delete=models.PROTECT, default=0)
      details = models.CharField(max_length=200, null=True)
      amount = models.DecimalField(decimal_places=2, max_digits=10)
      accountid = models.ForeignKey(account, on_delete=models.PROTECT)
  </code></pre>
  <p>And I'm getting a cumulative total for each date using a raw SQL query:</p>
  <pre><code>def cumulativebalance():
          query = &quot;&quot;&quot;SELECT id, transactiondate,
              SUM(&quot;amount&quot;) 
              OVER (ORDER BY transactiondate) AS &quot;cumsum&quot; 
              FROM &quot;transactions_transaction&quot;
              GROUP BY transactiondate 
              ORDER BY transactiondate ASC, &quot;cumsum&quot; ASC
              &quot;&quot;&quot;
          return balance = transaction.objects.raw(query)
  </code></pre>
  <p>This does return the output I'm looking for. My question is whether there's a way in Django that I can incorporate this into my model instead of having it as raw SQL?</p>
  <h2>What I've tried</h2>
  <p>I did find a previous <a href="https://stackoverflow.com/questions/43517901/cumulative-running-sum-with-django-orm-and-postgresql">similar question</a> and I've adapted the answer to my own model:</p>
  <pre><code>test = transaction.objects.annotate(
              cumsum=Func(
                  Sum('amount'), 
                  template='%(expressions)s OVER (ORDER BY %(order_by)s)', 
                  order_by=&quot;transactiondate&quot;
              ) 
          ).values('transactiondate', 'cumsum').order_by('transactiondate', 'cumsum')
  </code></pre>
  <p>But this gives me an error: OperationalError at /transactions/ near &quot;OVER&quot;: syntax error. I don't understand what this is telling me.</p>
  <p>The SQL which was generated is shown in the traceback. My best guess is that it should be PARTITION BY after OVER instead of ORDER BY, but I don't know how I could change that from my python code.</p>
  <pre><code>('SELECT &quot;transactions_transaction&quot;.&quot;transactiondate&quot;, '
   'CAST(CAST(SUM(&quot;transactions_transaction&quot;.&quot;amount&quot;) AS NUMERIC) OVER (ORDER '
   'BY transactiondate) AS NUMERIC) AS &quot;cumsum&quot; FROM &quot;transactions_transaction&quot; '
   'GROUP BY &quot;transactions_transaction&quot;.&quot;id&quot;, '
   '&quot;transactions_transaction&quot;.&quot;transactiondate&quot;, '
   '&quot;transactions_transaction&quot;.&quot;category_id&quot;, '
   '&quot;transactions_transaction&quot;.&quot;details&quot;, &quot;transactions_transaction&quot;.&quot;amount&quot;, '
   '&quot;transactions_transaction&quot;.&quot;accountid_id&quot; ORDER BY '
   '&quot;transactions_transaction&quot;.&quot;transactiondate&quot; ASC, &quot;cumsum&quot; ASC')
  </code></pre>