Ask Your Question
1

how to use the total of a sum function as part of another calulation in libreoffice base

asked 2020-10-22 22:25:48 +0100

Panchopar gravatar image

how to use the total of a sum function as part of another calculation in libreoffice base ver 6.4.6.2

edit retag flag offensive close merge delete

Comments

Please provide more complete information. What do you mean by 'another calculation'? What SQL query have you tried so far? Most likely everything will be done in a single SQL query.

frofa gravatar imagefrofa ( 2020-10-22 22:44:41 +0100 )edit

I am using a query on my form to show me the sum of the amount field in one of my tables. Form has 5 tables on it. This is for a Invoice for many customer. some have one line (1 amount) and some have 4 or 5 lines( 4 or 5 amounts) I need to get the total of these amounts and subtract some other costs to give me the profit on this customer.

Panchopar gravatar imagePanchopar ( 2020-10-22 22:58:30 +0100 )edit

I can get the total sum but it is a alias and it won't let me use alias fields as a part of a calculation.

Panchopar gravatar imagePanchopar ( 2020-10-22 23:00:28 +0100 )edit

Can you give us the query CODE please? Also, you might want to consider doing your TOTALS via a REPORT (using the Base Report Builder).

frofa gravatar imagefrofa ( 2020-10-23 00:19:08 +0100 )edit

SELECT "cusinfo"."cnum", "cusinv"."invnum", SUM( "invitem"."amount" ) "stotal", "total" - "ourcost" - "shipcost" - "motax" - "rsellfee" - "raffee" - "rsalestax" - "rbumpfee" "profit1" FROM "EddiesG"."cusinv" "cusinv", "EddiesG"."cusinfo" "cusinfo", "EddiesG"."invitem" "invitem", "EddiesG"."revinfo" "revinfo" WHERE "cusinv"."cnum" = "cusinfo"."cnum" AND "invitem"."invnum" = "cusinv"."invnum" AND "revinfo"."invnum" = "cusinv"."invnum" GROUP BY "cusinv"."invnum" ORDER BY "cusinfo"."cnum" ASC

Panchopar gravatar imagePanchopar ( 2020-10-23 18:38:02 +0100 )edit

should "stotal" be "total"? Any error messages? Also, the SUM function part may not be 'available' as an alias unless nested in an inner SELECT clause - assuming that inner clauses are calculated first by the SQL engine. (But I confess I am not an expert in SQL code.)

frofa gravatar imagefrofa ( 2020-10-24 05:42:28 +0100 )edit

stotal is the alias for the sum of the amount. total is another field in cusinv table.

Panchopar gravatar imagePanchopar ( 2020-10-27 05:05:55 +0100 )edit

What was the error code? Does the query work without the use of an alias?

frofa gravatar imagefrofa ( 2020-10-28 08:45:55 +0100 )edit

yes the query works fine but when I try to add a calculation using the alias it says can't find that field.

Panchopar gravatar imagePanchopar ( 2020-10-29 04:09:19 +0100 )edit

2 Answers

Sort by » oldest newest most voted
0

answered 2020-10-29 00:27:36 +0100

Ratslinger gravatar image

Hello,

Easiest method is simply duplicate:

SUM( "invitem"."amount" )

as a part of your wanted calculation such as:

SUM( "invitem"."amount" ) - "A" -"B" - "C" As "Profit"

The other method is to embed the select in another. Probably more trouble than it is worth.

edit flag offensive delete link more

Comments

Will try that. Thanks for the help

Panchopar gravatar imagePanchopar ( 2020-10-31 06:58:57 +0100 )edit
0

answered 2020-10-25 02:40:06 +0100

frofa gravatar image

See my comments above.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

2 followers

Stats

Asked: 2020-10-22 22:25:48 +0100

Seen: 80 times

Last updated: Oct 29 '20