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.