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

Creating tables and queries

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

Postby cdeavionics » Mon Dec 14, 2015 12:46 am

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

Postby Sliderule » Mon Dec 14, 2015 1:44 am

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. :super:
  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. :bravo:

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
User avatar
Sliderule
Volunteer
 
Posts: 1207
Joined: Thu Nov 29, 2007 9:46 am

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

Postby cdeavionics » Mon Dec 14, 2015 3:32 pm

Hi Sliderule,

I was impressed with your prompt, comprehensive and very helpful reply; thank you so much.
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

Postby Sliderule » Mon Dec 14, 2015 4:44 pm

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_Date
    GROUP 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_Date
    GROUP 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. :mrgreen:
  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. :bravo:
  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.
User avatar
Sliderule
Volunteer
 
Posts: 1207
Joined: Thu Nov 29, 2007 9:46 am

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

Postby cdeavionics » Mon Dec 14, 2015 7:30 pm

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

Postby Sliderule » Mon Dec 14, 2015 7:38 pm

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
User avatar
Sliderule
Volunteer
 
Posts: 1207
Joined: Thu Nov 29, 2007 9:46 am


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 1 guest