Groupt Query By Month

[Biolore]

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”

I blacked out stuff that isn’t dummy information

[Wildcard]

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(/7) to get the starting day of the week. No soap in Base.

[doug]

The functions that will accomplish this are WEEK(), MONTH(), MONTHBYNAME(), and YEAR(). To combine any two of them, do CONCAT(MONTHBYNAME(date), YEAR(date)). You might add a separator character, e.g., '-' which would require CONCATing two CONCAT statements because HSQLDB v1.8 only concats two strings at a time.

If you GROUP BY any concatenation of those functions, you will achieve the desired result. See HSQLDB v1.8 reference.