[Solved] Calculated fields in Database

Discuss the database features

[Solved] Calculated fields in Database

Postby Sailor455 » Sun Mar 31, 2019 11:09 pm

I am so sorry if this has been asked before but I can not find what i am looking for.
Have a simple Database With fields like sales tax and Premium paid , total cost of item, ( they are simple math calculations based on amounts in other fields) I would like to set them up to automatically calculate the correct amount.

But can not find out where to enter the formuala or how to set up the field.

Example. Sales tax= (Item cost x Qty) x 7.5%

Can this be done in an existing field in the table or do I need to have a field created in a form? Every thing i find is how to do it in a spreadsheet which is not how i want to keep the records.

Again very sorry sure it was asked but any help would be greatly appreciated.
Last edited by Hagar Delest on Mon Apr 01, 2019 8:00 am, edited 1 time in total.
Reason: tagged solved
openoffice 4.1.1
Sailor455
 
Posts: 5
Joined: Sun Mar 31, 2019 10:59 pm

Re: Calculated fields in Database

Postby Villeroy » Sun Mar 31, 2019 11:55 pm

Create a new query in SQL view.
Code: Select all   Expand viewCollapse view
SELECT *, "Item cost" * "Qty" * 0.075 AS "Sales tax" FROM "table_name"

where "Item cost" and "Qty" are actual column names and "table_name" is the actual name of the table.
This select all fields (*) from the specified table plus a calculated column labeled "Sales tax".
You can use the query in the same way as you can use the table. The calculated field is not editable.
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: 26727
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calculated fields in Database

Postby Sailor455 » Mon Apr 01, 2019 2:57 am

thanks ran a few test and think i see what i need to do... in the query view create the code for every field i want to be calculated. A lot different then the spreadsheet but get the idea for now.
Thanks again for the help greatly appreciated.
openoffice 4.1.1
Sailor455
 
Posts: 5
Joined: Sun Mar 31, 2019 10:59 pm

Re: [Solved] Calculated fields in Database

Postby Villeroy » Mon Apr 01, 2019 4:27 pm

This is easier to edit in SQL view without query designer.
Unlike spreadsheets, the calculation applies to all rows of a field.
You can be sure that the field works because all data types are correctly typed.
You can enforce that a field can not have empty values.
In case of an empty value, any calculation returns an empty value instead of error.
COALESCE("field", 1) returns 1 if "field" is empty.
Decimal fractions: 5/3 = 1 integer because both input values are integer.
5/3.00 = 1.67 because we use an explicit 2-digit value as divisor.
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: 26727
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Calculated fields in Database

Postby Sailor455 » Wed Apr 03, 2019 3:24 am

getting a syntax error even if i just substitute my field names with those in the statement. not sure why worked and now it will not.

SELECT *, "Item Cost" * "Qty" * 0.075 AS "Sales Tax" From "Inv"
errors
SQL Status: HY000
Error code: 1000

Syntax error in SQL expression

will not let me save it.
openoffice 4.1.1
Sailor455
 
Posts: 5
Joined: Sun Mar 31, 2019 10:59 pm

Re: [Solved] Calculated fields in Database

Postby Sliderule » Wed Apr 03, 2019 3:59 am

Use the following Query:

Code: Select all   Expand viewCollapse view
SELECT
   "Inv".*,
   "Item Cost" * "Qty" * 0.075 AS "Sales Tax"
FROM "Inv"

Explanation:
  1. Since your SELECT clause returns more than just the contents of each column in the table, the SQL ( Structured Query Language ) requirement is to include the table name before the * ( "Inv".*, ).

  2. You might want to consider the following . . . which adds a ROUND function to have the "Sales Tax" rounded to two decimal places. :)

    Code: Select all   Expand viewCollapse view
    SELECT
       "Inv".*,
       ROUND("Item Cost" * "Qty" * 0.075, 2) AS "Sales Tax"
    FROM "Inv"

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
User avatar
Sliderule
Volunteer
 
Posts: 1170
Joined: Thu Nov 29, 2007 9:46 am

Re: [Solved] Calculated fields in Database

Postby Sailor455 » Wed Apr 03, 2019 4:06 am

yes that will help also found other mistake.
Question, to do multi able Calculated fields they all must be in one string Correct?
Does SQL Look at "( )" and "[ ]" like a math formula will? working from the inside out?

With your help i have managed to make a statement to create four Calculated Fields. Thanks..
openoffice 4.1.1
Sailor455
 
Posts: 5
Joined: Sun Mar 31, 2019 10:59 pm

Re: [Solved] Calculated fields in Database

Postby Sliderule » Wed Apr 03, 2019 4:12 am

Question, to do multi able Calculated fields they all must be in one string Correct?

I do not understand your question, perhaps, the trailing comma after the ALIAS . . . where the Alias is the name of the new column in the Result Set, means, you could add another column.

Does SQL Look at "( )" and "[ ]" like a math formula will? working from the inside out?

You may use parentheses to indicate which operations you want to perform first. Without parentheses, as with other computer arithmetic operations, multiplication and division are performed prior to addition and subtraction.

Sliderule
User avatar
Sliderule
Volunteer
 
Posts: 1170
Joined: Thu Nov 29, 2007 9:46 am

Re: [Solved] Calculated fields in Database

Postby Sailor455 » Wed Apr 03, 2019 4:16 am

thanks again,,
looks like have a long statement to write as about 6 calculated columns :knock: :knock: :D
openoffice 4.1.1
Sailor455
 
Posts: 5
Joined: Sun Mar 31, 2019 10:59 pm

Re: [Solved] Calculated fields in Database

Postby Villeroy » Wed Apr 03, 2019 10:54 am

But you have to define this statements only once for all time which makes a huge difference to spreadsheets where you are constantly moving around formula cells.
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: 26727
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Base

Who is online

Users browsing this forum: No registered users and 1 guest