🗄️ [Documentation] - ActualQL aggregate documentation

[actualbudget]

🗄️ [Documentation] - ActualQL aggregate documentation

[bdoherty]

Here are a few examples that I have documented.

Total Amount per Payee between 6 Apr 2020 and 5 Apr 2021

(await $query($q('transactions')
.filter({ $and: [ {date: { $gte: '2020-04-06'}}, {date: {$lte: '2021-04-05'}}]})
.groupBy('payee.name')
.orderBy('payee.name')
.select(['payee.name', {'amount': {$sum: '$amount'}}])
)).data.map((row) => { console.log(`${row['payee.name']}: ${row.amount/100}`) })

Total Amount of all transactions with Note containing #interest (P) between 6 Apr 2020 and 5 Apr 2021

(await $query($q('transactions')
.filter({ '$and': [ 
  {'date': { '$gte': '2020-04-06'}}, 
  {'date': {'$lte': '2021-04-05'}}, 
  {'notes': {'$like': '%#interest (P)%'}} ]
})
.calculate({ '$sum': '$amount' })
)).data/100

or

(await $query($q('transactions')
.filter({ '$and': [ 
  {'date': { '$gte': '2020-04-06'}}, 
  {'date': {'$lte': '2021-04-05'}}, 
  {'notes': {'$like': '%#interest (P)%'}} 
]})
.select({ 'total': { '$sum': '$amount' } })
)).data[0].total/100

Total Amount per Category between 6 Apr 2020 and 5 Apr 2021

(await $query($q('transactions')
.filter({ $and: [ {date: { $gte: '2020-04-06'}}, {date: {$lte: '2021-04-05'}}]})
.groupBy('category.name')
.orderBy(['category.group.sort_order','category.sort_order'])
.select(['category.group.name','category.name', {'amount': {$sum: '$amount'}}])
)).data.map((row) => { console.log(`${row['category.group.name']}/${row['category.name']}: ${row.amount/100}`) })

[rich-howell]

Hey @bdoherty

Thanks for this, we have added this to the documentation, which is available here #35

Thanks again
Rich

[MatissJanis]

Closing as DONE