Ask Your Question

Decimal for Query

asked 2019-04-22 12:59:52 +0100

DucLe gravatar image

image description

I need to make a total as formula below keep keep decimal 3 places (means 0,###) both from table1 and Table2:

Total = Sub RM Cons * Sub RM Order Q'ty + Main RM Cons * Main RM Order Q'ty.

Thanks and best regards,


edit retag flag offensive close merge delete


I don't think you can answer it. Which field types did you use for Sub RM Cons; Sub RM Order Q'ty; Main RM Cons; Main RM Order Q'ty? Are they tables that are related or are they not otherwise related? Do you use HSQLDB, Firebird or others?

And you can upload a sample of your DB here. To upload, edit your question.

Here you can read the base documentation.

ebot gravatar imageebot ( 2019-04-22 14:46:57 +0100 )edit

2 Answers

Sort by » oldest newest most voted

answered 2019-04-22 20:45:42 +0100

Ratslinger gravatar image


Just a note, don't see any reason for decimal positions in quantity fields.

Here is the Query SQL statement needed:

SELECT "Main RM Items",
       CAST(("Main RM Cons"*"Main RM Order Qty") +("Sub RM Cons"*"Sub RM Order Qty") AS NUMERIC(10,3)) "Total"
      FROM "Table1"
        JOIN (SELECT * FROM "Table2") ON "Main RM Items" = "Sub RM Item")

Here is what results (using the data in your question):

image description

edit flag offensive delete link more

answered 2019-04-24 10:31:07 +0100

DucLe gravatar image

Dear Rastlinger & Harald Berger,

Thanks for your advices! They are so useful!

edit flag offensive delete link more



If an answer has solved your problem, please click on the large check mark in the circle to the left of this answer. It then shows the other participants that the question has been answered correctly.

ebot gravatar imageebot ( 2019-04-24 12:28:29 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-04-22 12:59:52 +0100

Seen: 69 times

Last updated: Apr 24