OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Cannot Sum a column of figures in a Query

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
elder73
General User
General User


Joined: 30 Apr 2009
Posts: 17

PostPosted: Wed May 16, 2012 4:09 pm    Post subject: Cannot Sum a column of figures in a Query Reply with quote

My table consists of: "ID" "Item" and "Amount"

When I go to the Query in Design view, and list all the above fields. In the "ID" column I list it as not showing.

The Table is listed as Family Data for all three columns

In the "Item" column it is listed as Visible and the function as Group.

The "Amount" column is listed as Visible and the function as Sum.

If I don't list anything under Alias the field name becomes SUM("FamilyData"."Amount") so a command is there on that column but nothing happens. When I run the Query all I get is a listing with no total in sight.

Any help would be really appreciated. Smile
Back to top
View user's profile Send private message Visit poster's website
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Thu May 17, 2012 9:35 am    Post subject: Reply with quote

Per your description ( which was very good, thank-you Smile ), I am not sure why the results returned to you is as described, unless, that calculated sums are all zero and you have formatted zero to display that way.

How about this, follow these instructions, and, see what is displayed. I am adding two additional columns:
  1. "Minimum"
  2. "Maximum"
just to see if they are calculated correctly Smile .

  1. Open your OpenOffice Base file ( *.odb )
  2. On the left, click on the Queries icon
  3. Under Tasks, click on: Create Query in SQL View...
  4. Copy and paste the following:
    Code:
    Select
         "FamilyData"."Itme"
       , SUM("FamilyData"."Amount") as "Sum"
       , MIN("FamilyData"."Amount") as "Minimum"
       , MAX("FamilyData"."Amount") as "Maximum"

    From "FamilyData" as "FamilyData"

    Group By "FamilyData"."Item"

  5. Run the Query, either:
    1. Press the F5 key
    2. Press the Run Query icon on the toolbar
    3. From the Menu: Edit -> Run Query

  6. You can SAVE the query if / when it works, OR, from the Toolbar icon, press: Switch Design View On/Off to see what it looks like if you had used the GUI ( Graphical User Interface ) to create it.

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.
Back to top
View user's profile Send private message
elder73
General User
General User


Joined: 30 Apr 2009
Posts: 17

PostPosted: Thu May 17, 2012 1:03 pm    Post subject: Reply with quote

Sliderule wrote:

How about this, follow these instructions, and, see what is displayed. I am adding two additional columns:
  1. "Minimum"
  2. "Maximum"
just to see if they are calculated correctly Smile .

[list=1][*]Open your OpenOffice Base file ( *.odb )
[*]On the left, click on the Queries icon
[*]Under Tasks, click on: Create Query in SQL View...
[*]Copy and paste the following:
Code:
Select
     "FamilyData"."Item"
   , SUM("FamilyData"."Amount") as "Sum"
   , MIN("FamilyData"."Amount") as "Minimum"
   , MAX("FamilyData"."Amount") as "Maximum"

From "FamilyData" as "FamilyData"

Group By "FamilyData"."Item"



Thank you for your reply. I did as you suggested and all it did was list out the items and the amounts. The Minimums and Maximums seemed to be OK. The name of the base file (.odb) iis "FamilyRecord". I replaced that in the above and it could not find anything. All of this leaves me with my head going around in circles. Smile

Hope you can help further.

Elder73
Back to top
View user's profile Send private message Visit poster's website
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Thu May 17, 2012 1:07 pm    Post subject: Reply with quote

I am sending you a PM ( Private Message ).

Sliderule
Back to top
View user's profile Send private message
elder73
General User
General User


Joined: 30 Apr 2009
Posts: 17

PostPosted: Fri May 18, 2012 2:17 pm    Post subject: [SOLVED] Reply with quote

This is how Sliderule solve my problem:


You could say that what you really wanted was a Query that included UNION. And, a UNION Query is a query that, because of the Base Parser, at the current time, it must be Run in Direct Mode.

The actual code I used was:


-- Query Written by Sliderule 2012-05-17
-- Uses a UNION Query, so, at the current time it must by run direct . . . passed to DB engine withOUT Base Parserr
Select
"Item",
"ID",
TO_CHAR("Date",'YY-MM-DD') as "Date",
"Amount",
"Income",
"Food",
"Gasoline",
"Auto",
"Rent",
"Insurance",
"Clothing",
"Misc",
"Tithe",
"Taxes"
FROM "FamilyData"

UNION ALL

SELECT
'** TOTAL **',
999999999,
'',
SUM("Amount"),
SUM("Income"),
SUM("Food"),
SUM("Gasoline"),
SUM("Auto"),
SUM("Rent"),
SUM("Insurance"),
SUM("Clothing"),
SUM("Misc"),
SUM("Tithe"),
SUM("Taxes")
FROM "FamilyData"

ORDER BY "ID"
Back to top
View user's profile Send private message Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group