How do I include results from only a certain month in Base?

[Nathan047]

I’m sorry if this has already been answered or if this is the wrong place to ask it, but I’ve done some searching and had no luck.

I’m new to SQL/Databases but I’m trying to generate a query to calculate changes to my budget in LibreOffice Base. Right now I’m tracking changes to my income like below:
chart

I have an SQL query set up via the LibreOffice editor to show the total amount I earned/lost and the reason for it below:
result

Now I’d like to be able to filter it by month (e.g. all changes from December but none from any other month). How do I go about doing that?

The raw SQL code I’ve got so far is:

SELECT “ChangeReason” AS “Change Reason”, SUM( “ChangeAmount” ) AS “Total Change” FROM “Dummy Budget” GROUP BY “ChangeReason”

However I am using the “edit” function, and my changes to it are here:

code screenshot

**UPDATE:

Thanks for the answer @Ratslinger, however, I’ve entered the changes but I’m only getting an error. It’s probably that I’m very new to SQL and messed something up, so if you could explain what I’m doing wrong that’d be great.

[Ratslinger]

Hello,

This is a common question. The result can be gotten in many ways but the most practical is by use of selection parameters. These are specific data used for comparison purposes and begin with the colon :. When the query is run and a parameter is detected the user is prompted for input. In the following statement I used two: one for a starting date anf one for an ending date. This allows you flexibility on what to choose each execution.

SELECT "ChangeReason" AS "Change Reason",
       SUM("ChangeAmount") AS "Total Change"
FROM (Select "ChangeReason", "ChangeAmount", "Date" from "Dummy Budget" Where "Date" >= :Begin_Date and "Date" <= :Ending_Date)
GROUP BY "ChangeReason"

This statement (interior portion) selects only those records which meet the criteria of the parameters and then your original statement bases the result on that selection. This statement can only be entered in a Query using the SQL View mode (my preferred method anyway).

A note: please do not post as a Wiki. It helps no one. Not in this case, but normally it really helps when you ask a question to include your OS, the LO version (actual version # - not current!) and in Base what DB you are using - your case the default embedded probably.

If this answers your question please click on the (upper left area of answer).

[Nathan047]

Hey, thanks for the answer. I’ve updated the code but now I’m getting an error (I’ve updated the post with more information). Also, I posted the question as a wiki that way if I used the wrong terminology somebody with more experience might be able to word the question in a way that would be easier for somebody who knows what they’re doing to understand.

[Ratslinger]

Note: Wiki does no good. Qualified people here can modify normal questions when necessary. Using Wiki hurts you as don’t receive karma (which gives capabilities) and other things.

Your problem is you changed my statement without knowing what you were changing. If you want to ‘hard code’ specific dates, replace that WHERE statement to:

  WHERE "Date" >= '2017-12-01' AND "Date" <= '2017-12-31'

[mikekaganski]

That is unfortunate that people suppose that wiki gives some advantages. It’s not so. Those who have more experience have enough points to edit anybody’s questions, not only wikis.

[Ratslinger]

@Nathan047 Perhaps you didn’t understand the statement I originally provided. If you enter AS IS, when executed it will prompt for from & thru dates. There you can enter 12/01/17 & 12/31/17. The method you are using will require you to change the SQL whenever you want different dates selected.

[librebel]

We could ask the user to repost his or her question as a normal Question, instead of a Wiki.