Ask Your Question
0

Need help with Sum Function in Libre Base

asked 2015-07-09 16:23:01 +0200

updated 2015-07-10 02:36:50 +0200

doug gravatar image

I have a simple table that has the following columns:

ID         Item        Amount        Quantity      Total       Grand total

The total is a calculated item as "Amount"*"Quantity" and works fine.

I have the following SQL:

SELECT "ID" AS "ID", "Item" AS "Item", "Amount" AS "Amount", "Quantity" AS "Quantity", "Amount" * "Quantity" AS "Total" FROM "Trial", "Grand Total" as Sum("Total") From "Trial"

What I am trying to do is Sum the Total column in the Query. For the life of me I can't figure out how to make that work.

Can someone help?

edit retag flag offensive close merge delete

Comments

the label as Sum("Total") also will cause problems. Avoid keywords in field headers For example, Base reports may choke on column labels that appear to be keywords or functions, will return a SQL error.

doug gravatar imagedoug ( 2015-07-10 02:42:16 +0200 )edit

This doesn't work. Also my Table is called Trial so I substituted that for Table1. Trying to post the screen shot of the error but don't know how to do that either.

Sparksvonrou gravatar imageSparksvonrou ( 2015-07-10 03:48:50 +0200 )edit

The text after the as is a label. Just type out the error message.

doug gravatar imagedoug ( 2015-07-10 07:21:34 +0200 )edit

Ok. Maybe I am missing something. I see this as Oracle Report Builder. I do not see that on my setup and neither do I see the function for reports.

Sparksvonrou gravatar imageSparksvonrou ( 2015-07-10 23:55:40 +0200 )edit

sounds like you are using apache open office, which is at least branded differently. Please confirm LibreOffice version using Help-> About

doug gravatar imagedoug ( 2015-07-11 04:06:22 +0200 )edit

Version: 4.2.8.2 Build ID: 420m0(Build:2)

Sparksvonrou gravatar imageSparksvonrou ( 2015-07-17 22:39:13 +0200 )edit

yes, after I commented I noticed that it sometimes does indeed have the title you report. Consider updating to 4.3 or 4.4, see if that helps with report builder.

doug gravatar imagedoug ( 2015-07-17 23:18:40 +0200 )edit

1 Answer

Sort by » oldest newest most voted
1

answered 2015-07-09 16:52:03 +0200

pierre-yves samyn gravatar image

updated 2015-07-10 11:10:59 +0200

Hi

Habit is to not store in tables that can be calculated in queries...

1st query (Total):

SELECT "id", "Item", "Amount", "Quantity", "Amount" * "Quantity" "Total" FROM "Table1"

2nd query (Grand Total):

SELECT SUM( "Total" ) "Grand Total" FROM "Total"

You can mix:

SELECT "Total"."id", "Total"."Item", "Total"."Amount", "Total"."Quantity", "Total"."Total", "GrandTotal"."Grand Total" FROM "GrandTotal", "Total"

GrandTotal.odb

[EDIT] I said that the habit is not to store the calculation results as the objective of normalization of databases is in particular to avoid data redundancy. Why store something we can recalculate? Caution, however, with Amounts, it is necessary to have a data model to manage price changes.

You can also produce Total & Grand Total directly in a report. See in the example: edit the report then open the Report Navigator and click Functions. I join a new version of GrandTotalReport.odb

screenshot

Regards

edit flag offensive delete link more

Comments

Ok See my comment on original post. But can you elaborate a little on "to not store in tables" ?

Sparksvonrou gravatar imageSparksvonrou ( 2015-07-10 03:50:50 +0200 )edit

Truthfully, I am considering going back to Access. It just seems unreal that it is so difficult to put a sum of a column in so it can be reported. I know I am new to LIbre but I am not a super Access user either but I can do it there. I just don't want to add a Windblows machine and have to get Access. :)

Sparksvonrou gravatar imageSparksvonrou ( 2015-07-10 03:53:43 +0200 )edit

See my edited answer (to be able to include files).

pierre-yves samyn gravatar imagepierre-yves samyn ( 2015-07-10 11:13:36 +0200 )edit

Thank you. I loaded the sample and understand now that putting the Sum function in a seperate table makes the Sum function work. For some reason the Report can not be edited or opened. Could you check that?

Sparksvonrou gravatar imageSparksvonrou ( 2015-07-11 17:28:18 +0200 )edit

Perhaps have you not install the optional component Report builder (custom installation lets you choose components to install)?

pierre-yves samyn gravatar imagepierre-yves samyn ( 2015-07-11 17:37:50 +0200 )edit

Ok. I don’t see the Report Builder in the Software Center.

Sparksvonrou gravatar imageSparksvonrou ( 2015-07-17 22:41:27 +0200 )edit

Finally got the 5.0 version installed and now it complicated things. I am not longer able to use the same process I did to create my reports. I tried copying previous reports and can't find any place they go or how to paste them to create the duplicate. Also I used to be able to paste my company logo into the header and I can't do that any more either. The entire display is now graphic block instead of the previous display for editing the reports.

Sparksvonrou gravatar imageSparksvonrou ( 2015-08-29 15:24:32 +0200 )edit

How do I get SQL view for the report I am editing??

Sparksvonrou gravatar imageSparksvonrou ( 2015-08-29 15:24:56 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-07-09 16:23:01 +0200

Seen: 3,634 times

Last updated: Jul 10 '15