Groupt Query By Month

asked 2013-07-04 05:57:26 +0100

Biolore gravatar image

Basically, I want to have a query that totals monthly income and expenses, but I can't seem to find a function that will change the date to a month/year format.

I've got one working for a grand total: SELECT SUM( "DepositAmount" ) AS "Deposit", SUM( "WithdrawAmount" ) AS "Withdraw", "AccountID", ( SUM( "DepositAmount" ) - SUM( "WithdrawAmount" ) ) AS "Total" FROM "Transactions" GROUP BY "AccountID"

This one will group all transactions from the same day, but I need to change it to be by month/year: SELECT SUM( "WithdrawAmount" ) AS "Withdraw", SUM( "DepositAmount" ) AS "Deposit", "Date", "AccountID", SUM( "DepositAmount" ) - SUM( "WithdrawAmount" ) AS "Total" FROM "Transactions" GROUP BY "date"{escape '/'}, "AccountID"

image description

I blacked out stuff that isn't dummy information

edit retag flag offensive close merge delete


I'd be interested in a solution to this as well. Date handling seems pretty fine-tuned for Calc and pretty clunky for Base. Possibly I'm just missing data on how to handle dates through Base (I'm not very experienced in it), but I'm running into similar problems. Specifically, I want to group dates by week, which involves some calculation but in Calc could be done by FLOOR(<date>/7) to get the starting day of the week. No soap in Base.

Wildcard ( 2015-02-01 09:22:03 +0100 )edit