OS/2, eCS & ArcaOS - Technical > Programming

SQL running total

(1/2) > >>

Per E. Johannessen:

This statement works with SQLite;

select amount, (select sum(t2.amount) from example t2 where t2.id <= t1.id) as accumulated from example t1 order by id

Does anyone know how to make it work with dBase (IV/V)?

(I've found many examples for other databases but none of them works with dBase.)

Doug Clark:
Yes - with an Intersolv ODBC dbase driver  (l2dbf12.dll date 10/18/1999)

The example below shows that sql statement, rewritten to  work with some
test data that I have.  rtotal = your accumulated (the running total), district = your amount, and
the table name is voters instead of your example.  I also added a
WHERE last_name='CLARK'  clause because my test data has 31k rows.  And it is
SLOW because of what it is doing.  You have a SELECT statement where
you have replaced a column name with another SELECT statement - so it is
executing a SELECT statement for each row that it retrieves from the first
SELECT statement.  BTW this is covered on page 274 of Joe Celko's SQL for Smarties book.

It is worth noting that the Borland Database Engine does not support this statement.

It is also worth noting that while this works, it probably doesn't really get you
what you want.  The column ID in your SQL (voter_id in mine) is a unique value.
It is unlikely the values in this column sort the data the way you want to view it,
and you must order on the column(s) in the <= part of the WHERE.

So the second image shows a variation of your and my first SQL statement.  Instead of
voter_id (or in your case ID), I am using last_name and first_name concatenated together.
CONCAT() is the scalar function that concatenates stuff together: the {fn }  part is
an ODBC escape clause that Intersolv wants around scalar functions.
Yes the Intersolv dBase ODBC driver supports the CONCAT() function as long as it is escaped.

The problem with this is more than one row in the data has the same first and last name.

What I really want to do is concatenate together last_name,first_name and voter_id, because
voter_id is unique for all rows in the data. But
I cannot do that because voter_id is a numeric column and you cannot concatenate together
two dissimilar data types.  And the Intersolv driver does not have a CAST() function for
changing the data type.

So yes the Intersolv driver will allow you to issue the SQL statement you have written and
it will get the correct data back.  But if you want to order the data differently you will
have to find a unique column, or combination of columns that uniquely identify each row, that
you can live with sorting by.

As for how you use the Intersolv driver.  The application I have attached screen shots of works
with Intersolv drivers - even evaluation drivers.  However it is months away from being released.
If you have a strong need to do this now, and are willing to live with half baked software that
will probably require you to reboot with a different LIBPATH statement while you are using it,
I will send you the app - send me a message or email.  Martin knows how to get ahold of me.

Per E. Johannessen:
Thanks for info, Doug.
I tried your SQL-statements using the drivers that are bundled with DBExpert, (from 1994) and it will not work.
Error message: "Mismatched parens". Have tried a number of variations with "(" and ")" but no success yet.
I will try some "evaluation" drivers as soon as I get the chance.
Nice to hear that your're working on a "database application". Right now I don't have the need to implement running totals through SQL.
since the Rexx script provided by Lars, in another post, works fine. 
Anyway, I'm very interested your application and will be happy to test when it is ready for that.

Per E. Johannessen:
Hi, Doug, sent you a pm.

Per E. Johannessen:
Some more testing ....

select a.rowid, a.accno, a.bkgdate, a.amount, (select sum(b.amount) from finance b where b.rowid <= a.rowid and a.accno = b.accno group by b.accno) from finance a order by a.accno

However, I also need "order by BKGDATE" but that will change the running total column and make it useless.

Have an idea about running an update query with the necessary "group by" and "order by" and that it inserts record number,
incremented by 1. in a new column.
Ideas anoyone?


[0] Message Index

[#] Next page

Go to full version