Ask Your Question

Groupt Query By Month

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

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 gravatar imageWildcard ( 2015-02-01 09:22:03 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2015-07-21 06:10:53 +0200

doug gravatar image

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools


Asked: 2013-07-04 05:57:26 +0200

Seen: 767 times

Last updated: Jul 21 '15