[Solved] Problems with SUM function and named parameters

[piper1970]

Hello,

I am trying to get a Query to post a sum of income amounts between two given date ranges. The table used has a _date field and an _amount field, of which I've been trying via SQL unsuccessfully to get to sum up my month's earnings.

Below is the SQL code I'm using, along with the error I'm receiving. My understanding of SQL select statements is slightly above beginner, but probably under that of a novice by a long shot. Aggregate functions (HAVING, GROUP BY, etc..) are a weak point in my comprehension of the language.

Code: Select all

SELECT SUM( '_amount' ) AS "Total", '_date' AS "Date" FROM "TableIncome" WHERE '_date' BETWEEN ["Enter Start Date"] AND ["ENTER END DATE"]

This produces the following error.....

Code: Select all

SQL Status: HY000
Error code: 1000

syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE

If read like pseudocode, my expectation is to input a starting and ending date, and get a grand total amount for output based on those two dates. However, this crashes. I've also tried with the Wizard and Design modes, but they don't seem to have anything that (in a self-explanatory fashion) would accomplish this task.

Any SQL hackers that are good with HSQL, I'd appreciate your help. This simple query will help me greatly with getting tax information handled.

Thanks

[Sliderule]

Welcome to OpenOffice Base.

You might want to try the following SQL code, by copy and paste it into:

1. Open your OpenOffice Base file ( *.odb )
2. Press the Queries icon on the left
3. Under Tasks, press Create Query in SQL View...

Code: Select all

SELECT 
   SUM( "_amount" ) AS "Total", 
   "_date" AS "Date" 
FROM "TableIncome" 
WHERE "_date" BETWEEN :Enter_Start_Date AND :ENTER_END_DATE
GROUP BY "_date"

Explanation:

1. I am assuming you are using HSQL as your database engine. You can confirm this by looking at the bottom, to the status bar, after opening your OpenOffice database file.
2. Table and column names are surrounded by double quotes ( " ) AND they must be entered EXACTLY as you have defined them in your table definitions, including CASE ( UPPER / Mixed / lower ) characters. For example, "_date" is not the same as "_Date".
3. Literals ( strings, dates, time ) are surrounded by single quotes ( ' )
4. For a Parameter Query . . . with a 'pop-up' that a user is prompted for entry, starts with a colon ( : ) is ONE WORD ( no spaces ) . . . for example: ( :Enter_Start_Date )
5. For an Aggregate Query, using functions like:
   
   1. SUM - Sum
   2. COUNT - Count
   3. MIN - Minimum
   4. MAX - Maximum
   5. STDDEV_POP - Standard Deviation of a Population
   6. STDDEV_SAMP - Standard Deviation of a Sample
   7. VAR_POP - Variance of a Population
   8. VAR_SAMP - Variance of a Sample
   . . . EACH item listed in your SELECT clause, must either be part of a GROUP BY clause, OR, contain one of the Aggregate Functions above. This would mean, the database will return the Aggregate Function(s) for each 'unique' "_date".

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved.

[piper1970]

Thanks, sliderule, for your help. I modified your code, as shown below, to meet the needs of what I was looking for.

Code: Select all

    SELECT
       SUM( "_amount" ) AS "Total",
       MONTHNAME("_date") AS "Date"
    FROM "TableIncome"
    WHERE "_date" BETWEEN :Enter_Start_Date AND :ENTER_End_Date
    GROUP BY MONTHNAME("_date")

Thank you for your help in understanding the rules of aggregate functions. This works out the way I need it, and will allow me to do the same for my expense query.