Expenses, Income and Loans/Mortgage monthly totals

21. February 2016

This query produces a table showing top level account monthly totals. A very high level summary over all time. FLATTEN puts each currency on its own line. Useful for saving as csv/pasting into a spreadsheet.

SELECT
    year, month, root(account, 1) as account, sum(position) as total
  FROM
    date > 2010-01-01 AND date < 2016-12-31
  WHERE
     account ~ "Expenses" OR
     account ~ "Liabilities:Mortgage" OR
     account ~ "Liabilities:Loan" OR
     account ~ "Income"
  GROUP BY year, month, account
  ORDER BY year, month, account
  FLATTEN