ExecuteSQL and Running Totals

[Sam Reicht]

 

Hi,

I need help with SQL and Running Total (instead of Total). 

 

TABLES

INV = Invoice table

- INV::Amount     ( Amount of Invoice)

- INV::DateMonth ( Month of InvoiceDate)

- INV::DateYear    ( Year of InvoiceDate)

 

DTE = Months table (fixed 12 records)

- DTE:Order=1...12)

- DTE::CurYear = 2016

 

 

Below SQL request works, see my result! But what I would require is a running total, adding each month sales total, see my "Required result"

 

ExecuteSQL ( "
SELECT      SUM(a.\"Amount\"/1000)
FROM          \"DTE\" b

LEFT JOIN   \"INV\" a
ON                 b.\"Order\"     = a.\"DateMonth\"
AND               b.\"CurYear\" = a.\"DateYear\"

GROUP BY    b.\"Order\"
"; " | " ; "0¶"  )

 

My current result as above SQL request
726.605050
818.7150
739.067850
0
703.7210
620.805350
599.986160
506.9230
597.8370
801.78250
1014.11510
567.64575

 

Below is what I require (a Running Total)
726.60505
1545.32005
2284.3879
2284.3879
2988.1089
3608.91425
4208.90041
4715.82341
5313.66041
6115.44291
7129.55801
7697.20376

 

I can do this without SQL, but I want to do using SQL and use it for my Charts.

Would appreciate any help. My SQL understanding is basic and I am using FileMaker 14 Adv

[Wim Decorte]

2 minutes ago, Sam Reicht said:

I can do this without SQL, but I want to do using SQL and use it for my Charts.

 

Why? 

What is your assumption about SQL being better?

[Sam Reicht]

Hi Wim,

 

Thanks for your interest. Its not better. But I can put the SQL statement inside the chart and show multiple charts at the same time on a single Layout! Without any additional fields, variables, relations etc.

[beverly]

if you can get the data without SQL, then store in variables for your charts.

otherwise, see if this self-join example gives you clues

https://www.wagonhq.com/blog/running-totals-sql