How to add up Table Fields?

Discuss the database features
Post Reply
wickerman
Posts: 6
Joined: Thu Sep 11, 2008 3:26 am
Location: Kendall, New York

How to add up Table Fields?

Post by 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
OOo 3.0.X on Ms Windows XP
User avatar
Villeroy
Volunteer
Posts: 30805
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to add up Table Fields?

Post by 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
wickerman
Posts: 6
Joined: Thu Sep 11, 2008 3:26 am
Location: Kendall, New York

Re: How to add up Table Fields?

Post by 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
OOo 3.0.X on Ms Windows XP
User avatar
Villeroy
Volunteer
Posts: 30805
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to add up Table Fields?

Post by Villeroy »

wickerman wrote: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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply