Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenSat, 31 Oct 2020 06:58:57 +0100how to use the total of a sum function as part of another calulation in libreoffice basehttps://ask.libreoffice.org/en/question/272821/how-to-use-the-total-of-a-sum-function-as-part-of-another-calulation-in-libreoffice-base/how to use the total of a sum function as part of another calculation in libreoffice base ver 6.4.6.2Thu, 22 Oct 2020 22:25:48 +0200https://ask.libreoffice.org/en/question/272821/how-to-use-the-total-of-a-sum-function-as-part-of-another-calulation-in-libreoffice-base/Comment by Panchopar for <p>how to use the total of a sum function as part of another calculation in libreoffice base ver 6.4.6.2</p>
https://ask.libreoffice.org/en/question/272821/how-to-use-the-total-of-a-sum-function-as-part-of-another-calulation-in-libreoffice-base/?comment=273906#post-id-273906yes the query works fine but when I try to add a calculation using the alias it says can't find that field.Thu, 29 Oct 2020 04:09:19 +0100https://ask.libreoffice.org/en/question/272821/how-to-use-the-total-of-a-sum-function-as-part-of-another-calulation-in-libreoffice-base/?comment=273906#post-id-273906Comment by frofa for <p>how to use the total of a sum function as part of another calculation in libreoffice base ver 6.4.6.2</p>
https://ask.libreoffice.org/en/question/272821/how-to-use-the-total-of-a-sum-function-as-part-of-another-calulation-in-libreoffice-base/?comment=273752#post-id-273752What was the error code? Does the query work without the use of an **alias**?Wed, 28 Oct 2020 08:45:55 +0100https://ask.libreoffice.org/en/question/272821/how-to-use-the-total-of-a-sum-function-as-part-of-another-calulation-in-libreoffice-base/?comment=273752#post-id-273752Comment by Panchopar for <p>how to use the total of a sum function as part of another calculation in libreoffice base ver 6.4.6.2</p>
https://ask.libreoffice.org/en/question/272821/how-to-use-the-total-of-a-sum-function-as-part-of-another-calulation-in-libreoffice-base/?comment=273583#post-id-273583stotal is the alias for the sum of the amount. total is another field in cusinv table.Tue, 27 Oct 2020 05:05:55 +0100https://ask.libreoffice.org/en/question/272821/how-to-use-the-total-of-a-sum-function-as-part-of-another-calulation-in-libreoffice-base/?comment=273583#post-id-273583Comment by Panchopar for <p>how to use the total of a sum function as part of another calculation in libreoffice base ver 6.4.6.2</p>
https://ask.libreoffice.org/en/question/272821/how-to-use-the-total-of-a-sum-function-as-part-of-another-calulation-in-libreoffice-base/?comment=272973#post-id-272973SELECT
"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" ASCFri, 23 Oct 2020 18:38:02 +0200https://ask.libreoffice.org/en/question/272821/how-to-use-the-total-of-a-sum-function-as-part-of-another-calulation-in-libreoffice-base/?comment=272973#post-id-272973Comment by frofa for <p>how to use the total of a sum function as part of another calculation in libreoffice base ver 6.4.6.2</p>
https://ask.libreoffice.org/en/question/272821/how-to-use-the-total-of-a-sum-function-as-part-of-another-calulation-in-libreoffice-base/?comment=273051#post-id-273051should "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.)Sat, 24 Oct 2020 05:42:28 +0200https://ask.libreoffice.org/en/question/272821/how-to-use-the-total-of-a-sum-function-as-part-of-another-calulation-in-libreoffice-base/?comment=273051#post-id-273051Comment by frofa for <p>how to use the total of a sum function as part of another calculation in libreoffice base ver 6.4.6.2</p>
https://ask.libreoffice.org/en/question/272821/how-to-use-the-total-of-a-sum-function-as-part-of-another-calulation-in-libreoffice-base/?comment=272840#post-id-272840Can you give us the query CODE please? Also, you might want to consider doing your TOTALS via a REPORT (using the Base Report Builder).Fri, 23 Oct 2020 00:19:08 +0200https://ask.libreoffice.org/en/question/272821/how-to-use-the-total-of-a-sum-function-as-part-of-another-calulation-in-libreoffice-base/?comment=272840#post-id-272840Comment by Panchopar for <p>how to use the total of a sum function as part of another calculation in libreoffice base ver 6.4.6.2</p>
https://ask.libreoffice.org/en/question/272821/how-to-use-the-total-of-a-sum-function-as-part-of-another-calulation-in-libreoffice-base/?comment=272834#post-id-272834I 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.Thu, 22 Oct 2020 23:00:28 +0200https://ask.libreoffice.org/en/question/272821/how-to-use-the-total-of-a-sum-function-as-part-of-another-calulation-in-libreoffice-base/?comment=272834#post-id-272834Comment by Panchopar for <p>how to use the total of a sum function as part of another calculation in libreoffice base ver 6.4.6.2</p>
https://ask.libreoffice.org/en/question/272821/how-to-use-the-total-of-a-sum-function-as-part-of-another-calulation-in-libreoffice-base/?comment=272832#post-id-272832I 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.Thu, 22 Oct 2020 22:58:30 +0200https://ask.libreoffice.org/en/question/272821/how-to-use-the-total-of-a-sum-function-as-part-of-another-calulation-in-libreoffice-base/?comment=272832#post-id-272832Comment by frofa for <p>how to use the total of a sum function as part of another calculation in libreoffice base ver 6.4.6.2</p>
https://ask.libreoffice.org/en/question/272821/how-to-use-the-total-of-a-sum-function-as-part-of-another-calulation-in-libreoffice-base/?comment=272829#post-id-272829Please 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.Thu, 22 Oct 2020 22:44:41 +0200https://ask.libreoffice.org/en/question/272821/how-to-use-the-total-of-a-sum-function-as-part-of-another-calulation-in-libreoffice-base/?comment=272829#post-id-272829Answer by Ratslinger for <p>how to use the total of a sum function as part of another calculation in libreoffice base ver 6.4.6.2</p>
https://ask.libreoffice.org/en/question/272821/how-to-use-the-total-of-a-sum-function-as-part-of-another-calulation-in-libreoffice-base/?answer=273879#post-id-273879Hello,
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.Thu, 29 Oct 2020 00:27:36 +0100https://ask.libreoffice.org/en/question/272821/how-to-use-the-total-of-a-sum-function-as-part-of-another-calulation-in-libreoffice-base/?answer=273879#post-id-273879Comment by Panchopar for <p>Hello,</p>
<p>Easiest method is simply duplicate:</p>
<pre><code>SUM( "invitem"."amount" )
</code></pre>
<p>as a part of your wanted calculation such as:</p>
<pre><code>SUM( "invitem"."amount" ) - "A" -"B" - "C" As "Profit"
</code></pre>
<p>The other method is to embed the select in another. Probably more trouble than it is worth.</p>
https://ask.libreoffice.org/en/question/272821/how-to-use-the-total-of-a-sum-function-as-part-of-another-calulation-in-libreoffice-base/?comment=274265#post-id-274265Will try that. Thanks for the helpSat, 31 Oct 2020 06:58:57 +0100https://ask.libreoffice.org/en/question/272821/how-to-use-the-total-of-a-sum-function-as-part-of-another-calulation-in-libreoffice-base/?comment=274265#post-id-274265Answer by frofa for <p>how to use the total of a sum function as part of another calculation in libreoffice base ver 6.4.6.2</p>
https://ask.libreoffice.org/en/question/272821/how-to-use-the-total-of-a-sum-function-as-part-of-another-calulation-in-libreoffice-base/?answer=273168#post-id-273168See my comments above.Sun, 25 Oct 2020 02:40:06 +0100https://ask.libreoffice.org/en/question/272821/how-to-use-the-total-of-a-sum-function-as-part-of-another-calulation-in-libreoffice-base/?answer=273168#post-id-273168