How to add up Table Fields?

[wickerman]

I am new at this. I can create a customer data table within the data base. I can create a work in progress table that shows one to several pieces of work per customer, and create a relationship for the two tables. I can create a form that shows the customer in block form (individual windows for each field) and the associated work in progress in tabular form (as in a spreadsheet). But I haven't the foggiest where to start to add up the amount column, add tax, and give a grand total as in a normal billing scenario. Can anyone help?
Thanks,
Wickerman

[Villeroy]

Aggregations:
Create queries in SQL view, substituting the double-quoted names with your actual ones:

Code: Select all

SELECT "Name", "Region", SUM("Sales") AS "Amount" FROM "TableX" GROUP BY "Name", "Region"

This will show the sum of sales for each combination (group) of "Names" and "Region". The GROUP BY clause is required for every selected field that is not aggregated by a function. Every row of the resultset may represent more than one row in the original table (e.g. 12 sales of Smith in Colorado amounting to $1,234.99), so it is not possible to edit this derived results (like you can not edit the result of a calculation).
Aggregating functions are SUM, AVG, MIN, MAX, COUNT all other fields need to be mentioned in the GROUP BY clause since they can not be shown row by row with the aggregated results.

Disregard all sales <=5:

Code: Select all

SELECT "Name", "Region", SUM("Sales") AS "Amount" FROM "TableX" GROUP BY "Name", "Region" WHERE "Sales">5

Disregard all aggregated amounts <=5:

Code: Select all

SELECT "Name", "Region", SUM("Sales") AS "Amount" FROM "TableX" GROUP BY "Name", "Region" HAVING "Amount">5

You can use this simple stuff in the graphical query designer as well, but I do not find it easier.

Another example with a calculated field Volume=Length*Width*Height in a query. The query is bound to a sub-form.
http://user.services.openoffice.org/en/ ... hp?id=1266
There is no aggregation of records. Every volume is derived from one distinct record with width, height and lenght, so the recordset is editable in principle except for the calculated result and the auto-id.

[wickerman]

Thank you for answering. I will give this a try. I have the handbook "Learning SQL" and need to delve deeper into it.
Thanks,
Wickerman

[Villeroy]

Thank you for answering. I will give this a try. I have the handbook "Learning SQL" and need to delve deeper into it.
Thanks,
Wickerman

While you are in the matter ... http://user.services.openoffice.org/en/ ... =83&t=9871