Dan Lewis wrote:
On Wednesday May 16 2007 4:23 am, Frank Schönheit - Sun Microsystems
Germany wrote:
Hi Dan,
Maybe I do not understand SQL well enough, but I have a
problem. I have a table for fuel that I buy as well as the
odometer reading when it is bought. I would like to calculate the
fuel economy (quantity of fuel divided by the distance traveled).
How do I subtract the odometer readings from adjacent rows to get
the distance traveled? I have a few other databases which require
subtracting one row of a field from other row.
speaking strictly, relational database tables don't have a concept
like "subsequent rows". That is, even if you insert rows in a
specific order, you're not guaranteed they're returned in the same
order - the database engine is free to change it. (In fact, some
known engines do.)
Okay, that was the nitpicking part, since you of course have an
additional field (the date when you bought the fuel) which defines
an "order" in the data records.
However, standard SQL does not provide a possibility to refer to
previous rows - that is, you cannot build an SQL query which
subtracts the current row value from the previous row value (or the
other way round).
Such tasks are best solved in the reporting domain. Now, well, the
reporting facilities which come with OOo 2.2 are not able to do
this. Sad enough. So, if you're bound to 2.2, the only suggestion I
(don't like to, but) can give: create a data range in a
spreadsheet, which is filled from your table, and do the
calculations in the spreadsheet.
Besides this, I want to point you to the new report designer which
will be available in 2.3. Therein, you will be able to solve this
problem.
If you're a bleeding-edge type, I suggest you wait for the
announcement [1] of a public alpha (this week, hopefully), and try
it out - every feedback in this early development phase is be
appreciated, and will help us to improve the designer before the
first public release.
Ciao
Frank
<snip>
I think I have now found a way, but it involves an extra "step"
(actually an extra query). Create a table with the following fields:
FuelID (PK), Date, FuelCost, FuelQuanity, and Odometer. Then create a
query (Query1) with these fields from the table: FuelID, FuelQuanity,
and Odometer. Create a second query (Query2). Its fields are:
Fuel.Date, Fuel.FuelCost, Query1.FuelQuanity,
(Query1.Odometer-Fuel.Odometer)/Query1.FuelQuanity, and
Query1.FuelID-Fuel.FuelID. Set two Criterion: Query1.FuelID -
Fuel.FuelID = 1; and
(Query1.Odometer-Fuel.Odometer)/Query1.FuelQuanity >0.
I can now use information from two consecutive rows to make
calculations. By the way, my background is a BS degree in mathematics
which included courses in topology. (Relational databases seem to
work similar to a topology.) I have a MS degree in Education with
emphasis in mathematics. I was hoping to be able to do it one one
query, but no such luck. With my math background, I was wondering if
it could then be done in several steps. It appears this is the case.
Actually, that is not a general answer.
You are assuming that the FuelID field has some sequence and there is no
such guarantee.
Even using an Autoincrement datatype.
Say you forget to enter the information for a given day,
Then enter the information for days following, later catch your mistake
and enter the old days values.
The difference between FuelID values will be something greater then 1.
That said, it is not an uncommon technique
Just recognize that you are building in dependencies external from the
schema.
Fail to enter the data in the correct sequence and the results of your
query will be erroneous.
Drew
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
