Function Use On Fields

dstrang · Newbie Joined: 16 Jun 2010 Posts: 4

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

RPG · Posted: Wed Jun 16, 2010 4:50 pm Post subject:

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

dstrang · Newbie Joined: 16 Jun 2010 Posts: 4

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

RPG · Posted: Thu Jun 17, 2010 1:19 am Post subject:

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

Felix Yan · Newbie Joined: 24 Jun 2010 Posts: 4

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.