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

English
#1

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

how to take the results of a calulation (alias) and store it into a field in a table
#2

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.

#3

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.

#4

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.

#5

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).

#6

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

#7

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.)

#8

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

#9

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

#10

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

#11

See my comments above.

#12

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.

#13

Will try that. Thanks for the help

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.