Need help with Sum Function in Libre Base

[Sparksvonrou]

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?

[doug]

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.

[Sparksvonrou]

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.

[doug]

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

[Sparksvonrou]

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.

[doug]

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

[Sparksvonrou]

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

[doug]

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.

[PYS]

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

Regards

[Sparksvonrou]

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

[Sparksvonrou]

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.

[PYS]

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

[Sparksvonrou]

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?

[PYS]

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

[Sparksvonrou]

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

[Sparksvonrou]

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]

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