Log in

View Full Version : Database and SUM() / GROUP BY SQL commands


andydempsey
07-23-2003, 05:24 PM
Is there any database program for the PPC that supports SUM() and GROUP BY in SQL statements?

I'm trying to build a couple of small apps for my own use and each DB package I look at fails at the ability to to what essentiall amounts to a percent to total for each database record (or set of records). Is this a pocket access limitation?

THe closest I have found so far is Sprint DB, but the sum function is done in a MACRO and not SQL, so too much programming is required on a case by bcase basis. Otherwise, am I forced to try something like NSBasic (but I'm not a programmer!)

Kati Compton
07-23-2003, 07:35 PM
I think abcDB does.

andydempsey
07-23-2003, 08:15 PM
Similar to SprintDB, adcDB uses a database function or macro in order to achieve sums. The manual has the following:

This function will retrieve the SUM of the fields retrieved by the SQL
statement.
TSUM(Field , SQL , Server)
PARAMETERS
Field
The field you wish to find the sum of. (Must be an INTEGER field)
SQL
The SQL statement which determines which records to find the sum of.
Server
The Server used to query against .. use --- for the local device
Example:
Save the sum value of FIELD1 into the textbox with tag name of MYTEXT.
SETVALUE(~mytext~//TSUM({field1},"SELECT field1 FROM mytable","---"))

-------------------------------------

I am trying to achive simple analytical SQL, where subquery 1 returns a set of data, subquery 2 returns the sum total for that set whilst the outer query joins the two subqueries together in order to present all the records from subquery 1 with an additional field of percent ot total... (hence the need for subquery 2).

Think of a timesheet appliction which records hours, but needs to report %