How to add up Table Fields?

Discuss the database features

How to add up Table Fields?

Postby wickerman » Mon Sep 15, 2008 10:35 pm

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
wickerman
 
Posts: 6
Joined: Thu Sep 11, 2008 3:26 am
Location: Kendall, New York

Re: How to add up Table Fields?

Postby Villeroy » Tue Sep 16, 2008 12:08 am

Aggregations:
Create queries in SQL view, substituting the double-quoted names with your actual ones:
Code: Select all   Expand viewCollapse view
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   Expand viewCollapse view
SELECT "Name", "Region", SUM("Sales") AS "Amount" FROM "TableX" GROUP BY "Name", "Region" WHERE "Sales">5


Disregard all aggregated amounts <=5:
Code: Select all   Expand viewCollapse view
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.
download/file.php?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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27299
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to add up Table Fields?

Postby wickerman » Tue Sep 16, 2008 2:51 am

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
wickerman
 
Posts: 6
Joined: Thu Sep 11, 2008 3:26 am
Location: Kendall, New York

Re: How to add up Table Fields?

Postby Villeroy » Thu Sep 18, 2008 6:13 pm

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 ... viewtopic.php?f=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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27299
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Base

Who is online

Users browsing this forum: No registered users and 3 guests