## [Solved] Simplest of queries - how to get a Sum calculation

Creating tables and queries

### [Solved] Simplest of queries - how to get a Sum calculation

Hi,

I recently decided to try to formulate a Query in Open Office Base as a complete neophyte and, approaching 80 years old, I am having trouble getting a very simple query to work. I have spent many hours trawling the Internet to find an answer without success, and also bought a textbook on O.O.Base which was of no help.

I have a 5-field database named "Spend"for my expenditure, with headings ;
"When" (Format date)
"Amount (£)" (Format Currency)
"Method" (Format Text)
"Details" (Format Text)

In the "Method" column I enter "Credit card", "Debit card" or "Cash" for each purchase as appropriate.

Quite simply, I am trying to automate getting a Sum of all the "Cash" purchases between two given dates each month

I have tried using the Query Wizard and the Design View, but all I have succeeded in doing is getting a result that gives me all the "Cash" purchases individually between the two dates but not the Sum.

The SQL printout is as follows:
SELECT "Method", "When", SUM( "Amount (£)" ), "Details" FROM "Spend" GROUP BY "Method", "When", "Details" HAVING ( ( "Method" = 'Cash' AND "When" > {D '2015-10-15' } AND "When" < {D '2015-11-14' } ) )

I would be most appreciative of. any guidance anyone can provide, and I apologise if a similar query has been raised before.
Last edited by cdeavionics on Mon Dec 14, 2015 7:39 pm, edited 1 time in total.
0pen Office 4.1.1 on Windows 8.1
cdeavionics

Posts: 5
Joined: Sat Feb 21, 2015 1:59 pm

### Re: Simplest of queries - how to get a Sum calculation

Please try the following Query and/or Queries:
Code: Select all   Expand viewCollapse view
`SELECT    "Method",    MIN("When") AS "From Date",    MAX("When") AS "To Date",    SUM("Amount (£)") AS "Sum Amount"FROM "Spend"GROUP BY "Method"HAVING (      (         "Method" = 'Cash'         AND "When" > {D '2015-10-15' }         AND "When" < {D '2015-11-14' }         )      )`

Explanation: Since you are using an Aggregate Query . . . that is . . . asking the database back-end to Group ( SUM or MIN or MAX or AVG ) . . . for each unique value(s) . . . your result table by certain criteria, you have to tell the database which are the GROUP BY columns. In this instance, I suspect you only want to see the TOTAL . . . but . . . not the both the details and total. Therefore, you can use the above SQL, or, one of the Queries below.

1. Prompt for a range of dates:

Code: Select all   Expand viewCollapse view
`SELECT    "Method",    MIN("When") AS "From Date",    MAX("When") AS "To Date",    SUM("Amount (£)") AS "Sum Amount" FROM "Spend"GROUP BY "Method"HAVING (      (         "Method" = 'Cash'         AND "When" > :Enter_From_Date         AND "When" < :Enter_To_Date         )      )`

2. Prompt for date ranges, and, return results for each unique "Method" ( not only 'Cash' )

Code: Select all   Expand viewCollapse view
`SELECT    "Method",    MIN("When") AS "From Date",    MAX("When") AS "To Date",    SUM("Amount (£)") AS "Sum Amount"FROM "Spend"GROUP BY "Method"HAVING (      (         "When" > :Enter_From_Date         AND "When" < "Enter_To_Date         )      )`
Explanation: To use any of the above Queries . . .
1. Start your database file ( *.odb )
2. Click on Queries on the left.
3. Click on Create Query in SQL View... under Tasks
4. Copy and Paste the Query
5. Run it ( F5 or, Run Query icon on toolbar, or, from the Menu: Edit -> Run Query )
6. Save the Query if you desire
7. You may ( your choice ) change the both from > to >= and/or < to <= depending if you wish the database to include the date in the range you have chosen.
8. Smile and say: "Gee Sliderule, that was easy. Now, all I have to do is mark the forum as [Solved]
I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

Sliderule
Volunteer

Posts: 1207
Joined: Thu Nov 29, 2007 9:46 am

### Re: Simplest of queries - how to get a Sum calculation

Hi Sliderule,

Unfortunately, your suggested programs produced Error messages.

I copied and pasted your first SQL program and ran it, but I got the following Error message (copied literatim):

The data content could not be loaded.

Not in aggregate function or group by clause:
org.hsqldb.Expression@23df28 in statement [SELECT "Method",
MIN("When") AS "From Date", MAX("When") AS "To Date",
SUM(Amount(£)" AS "Sum Amount" FROM "Spend" GROUP BY
"Method" HAVING ( ("Method = 'Cash' AND "When" > '2015-10-
15' AND "When" < '2015-11-14' ) ) ]

I then copied, pasted and ran your second SQL program. It ran the Dates input dialogue OK but then gave the following Error message (copied literatim):

Not in aggregate function or group by clause:
org.hsqldb.Expression@1cebfbf in statement [SELECT "Method",
MIN( "When") AS "From Date, MAX("When") AS "To Date",
SUM("Amount(£)") AS "Sum Amount" FROM "Spend" GROUP BY
"Method" HAVING ( ("Method" = 'Cash' AND "When" >
:Enter_From_Date AND "When" < :Enter_To_Date ) )]

I checked the pasted SQL code and it was exactly as you had provided (obviously, I suppose). I also re-ran my original query and this still gave the correct list of entries, but without any SUM result.

If you can afford the time, I would be grateful for any further guidance you can give me.

Cheers.
0pen Office 4.1.1 on Windows 8.1
cdeavionics

Posts: 5
Joined: Sat Feb 21, 2015 1:59 pm

### Re: Simplest of queries - how to get a Sum calculation

Please try the following Query and/or Queries:

1. Hard coded range of dates, for 'Cash' only:

Code: Select all   Expand viewCollapse view
`SELECT    "Method",    MIN("When") AS "From Date",    MAX("When") AS "To Date",    SUM("Amount (£)") AS "Sum Amount"FROM "Spend"WHERE "Method" = 'Cash'  AND "When" BETWEEN {D '2015-10-15'} AND {D '2015-11-14' }GROUP BY "Method"`

2. Prompt for a range of dates, only return a row for 'Cash':

Code: Select all   Expand viewCollapse view
`SELECT    "Method",    MIN("When") AS "From Date",    MAX("When") AS "To Date",    SUM("Amount (£)") AS "Sum Amount"FROM "Spend"WHERE "Method" = 'Cash'  AND "When" BETWEEN :Enter_From_Date AND :Enter_To_DateGROUP BY "Method"`

3. Prompt for date ranges, and, return results for each unique "Method" ( not only 'Cash' ):

Code: Select all   Expand viewCollapse view
`SELECT    "Method",    MIN("When") AS "From Date",    MAX("When") AS "To Date",    SUM("Amount (£)") AS "Sum Amount"FROM "Spend"WHERE "When" BETWEEN :Enter_From_Date AND :Enter_To_DateGROUP BY "Method"`
Explanation:
1. The above Queries, are now NOT using the HAVING clause, but, rather the filtering is a part of the WHERE clause.
2. Since the Queries are using BETWEEN for the "When" range . . . the values you enter for the dates will be included as a part of the SUM.
3. I am assuming, in your table "Spend" the column "When" is defined as a column type of DATE ( NOT a text column ), and the column "Amount (£)" is defined as a Decimal or Number or Interger ( NOT a text column).

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

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

Sliderule
Volunteer

Posts: 1207
Joined: Thu Nov 29, 2007 9:46 am

### Re: Simplest of queries - how to get a Sum calculation

Hi Sliderule,

Brilliant, you have solved it - all three of your programs work fine.

I don't know how to thank you - there is no way I could have sorted that out by myself!

On second thoughts, perhaps there is a way; if you tell me what your favourite Charity is, I'll make a £20 donation to it.

Thank you for all your time and effort.

Cheers
0pen Office 4.1.1 on Windows 8.1
cdeavionics

Posts: 5
Joined: Sat Feb 21, 2015 1:59 pm

### Re: Simplest of queries - how to get a Sum calculation

I am very glad that the Query/Queries work for you. And, your marking this as [Solved] is thanks enough.

I too, would like to thank-you for including the information about your database in describing the situation, so, we ( forum members ) could help you find the solution. Responding accordingly, on a timely basis, is the only thanks I need, but, appreciate your offer.

Cheers,

Sliderule

Sliderule
Volunteer

Posts: 1207
Joined: Thu Nov 29, 2007 9:46 am