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

Function Use On Fields

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


Joined: 16 Jun 2010
Posts: 4

PostPosted: Wed Jun 16, 2010 2:22 pm    Post subject: Function Use On Fields Reply with quote

I've been playing with Base as a means of learning database fundamentals. My sample flat-data is a record of my bike rides (there are 4 bikes listed) for the past 14 years. Column 1 is the date, column 2,3,4,5 are the miles for each bike. This is how the data is set up in Excel/Calc. What i would like to do is extract the total of the bikes for certain time periods (week month etc).

When I run a query with a sum for any bike column, it just returns the date with the corresponding ride mileage. How do I do a sum of any of the bike fields?

Is my data format working against me?

I have: ID--Date--Bike 1 Miles---Bike 2 Miles--Bike 3 Miles--Bike 4 Miles

Would the following be better:
ID--Date--Bike Name--Ride Miles

Any help would be appreciated.

Regards,
Dave
Back to top
View user's profile Send private message Send e-mail
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Wed Jun 16, 2010 4:50 pm    Post subject: Reply with quote

Hello

It is unclear what database engine you use?

I think you use a spreadsheet as base for your database.
when this is true I think it will not work for you.
As long you have a simple database and also simple question to the table there is no need to change it.

Only when you feel needs to study the SQL code then there is maybe a reason to transfer your data to a real database.
When you use the HSQLDB, see left down your window, then you can use the group command and the sum function.

Romke
Back to top
View user's profile Send private message
dstrang
Newbie
Newbie


Joined: 16 Jun 2010
Posts: 4

PostPosted: Wed Jun 16, 2010 9:09 pm    Post subject: Reply with quote

Thanks for the reply but I'm not sure you have a grasp of what I'm looking for.

I'm using Base as a means of learning database fundamentals. I've growing tired of the sample databases of address books so I took my bike riding data from a spreadsheet and created a flat database with it using Base. Base is based on the HSQL database engine.

I have: index || Date ||| Bike 1 Miles || Bike 2 Miles || Bike 3 Miles || Bike 4 Miles

with more than 500 rows of data. With the above data, I would like to run queries that sum up the ride mileage for given time periods. My current efforts have the sum only returning the value for each ride it returns. It returns an indeterminate miles, not the sum of the miles. Using the design view for the query, I select Date and set the Group function and I then select Bike 1, set an alias and select Sum.

What am I doing wrong?

From SQL view:
SELECT "BikeHistory"."Date", "BikeHistory"."Y22", SUM( "BikeHistory"."Y22" ) AS "Total" FROM "BikeHistory" AS "BikeHistory", "Bikes" AS "Bikes" GROUP BY "BikeHistory"."Date", "BikeHistory"."Y22" HAVING ( ( "BikeHistory"."Y22" > 0 ) )

Regards,
Dave

PS: I'm using OpenOffice Base 3.1.1
Back to top
View user's profile Send private message Send e-mail
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Thu Jun 17, 2010 1:19 am    Post subject: Reply with quote

Hello

http://wiki.services.openoffice.org/wiki/Database
http://documentation.openoffice.org/servlets/ProjectDocumentList?folderID=778
http://www.dbbm.fiocruz.br/class/Lecture/d17/sql/jhoffman/sqltut.html

There you can find information.
Also try to use the GUI for query's and study what you can find in the help file.
I think the author Sliderule does have explain all thing you need. Search on his name and also function you need.

It is outside my knowledge to give more answers maybe other person can do it.

Romke
Back to top
View user's profile Send private message
Felix Yan
Newbie
Newbie


Joined: 24 Jun 2010
Posts: 4

PostPosted: Tue Jul 17, 2012 6:54 pm    Post subject: Reply with quote

Sorry, this post was more than two years old.

The SQL query is not correct:
SELECT "BikeHistory"."Date", "BikeHistory"."Y22", SUM( "BikeHistory"."Y22" ) AS "Total" FROM "BikeHistory" AS "BikeHistory", "Bikes" AS "Bikes" GROUP BY "BikeHistory"."Date", "BikeHistory"."Y22" HAVING ( ( "BikeHistory"."Y22" > 0 ) )

When you have two tables, you need to join them using a WHERE or JOIN clause. Otherwise, you will end up having a Cartesian product, i.e. more rows than you intend.

Try removing the Bikes table and see how it goes.

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