Results are varying with same query

Hi I stuck in strange issue with one of my query.

Formula : (28800/3600-(28382.6/3600-(1920/3600+1860/3600))) =

So, Input Equation:

= (28800/3600-(28382.6/3600-(1920/3600+1860/3600)))

= (28800/3600-(28382.6/3600-(0.53333333333333+1860/3600)))

= (28800/3600-(28382.6/3600-(0.53333333333333+0.51666666666667)))

= (28800/3600-(28382.6/3600-(1.05)))

= (28800/3600-(28382.6/3600-1.05))

= (28800/3600-(7.8840555555556-1.05))

= (28800/3600-(6.8340555555556))

= (28800/3600-6.8340555555556)

= (8-6.8340555555556)

= (1.1659444444444)

= 1.1659444444444

But I am getting 0 as output from my query, it supposed to print 1

``````select (to_char (NULLIF(GREATEST(coalesce(28800/3600,0) - (coalesce(28382.6/3600,0) -
(coalesce(1920/3600,0) + (coalesce(1860/3600,0)))),0),0),'FM99,999,999,999'))::character varying as mydatas
``````

Now, same formula and same query with different values working fine. please check following.

(28800/3600-(13552.24/3600-(900/3600+0/3600)))

Input Equation:

= (28800/3600-(13552.24/3600-(900/3600+0/3600)))

= (28800/3600-(13552.24/3600-(0.25+0/3600)))

= (28800/3600-(13552.24/3600-(0.25+0)))

= (28800/3600-(13552.24/3600-(0.25)))

= (28800/3600-(13552.24/3600-0.25))

= (28800/3600-(3.7645111111111-0.25))

= (28800/3600-(3.5145111111111))

= (28800/3600-3.5145111111111)

= (8-3.5145111111111)

= (4.4854888888889)

= 4.4854888888889

so my query also gives 4 as output

``````select (to_char (NULLIF(GREATEST(coalesce(28800/3600,0) - (coalesce(13552.24/3600,0) -
(coalesce(900/3600,0) + (coalesce(0/3600,0)))),0),0),'FM99,999,999'))::character varying as mydatas
``````
• You are using integer arithmetic. You need to cast at least one of the numbers to `numeric` or make it a non-integer literal by append `.0` , e.g. `2880.0`
– user1822
Commented Jul 24, 2020 at 17:30
• @a_horse_with_no_name how to do that, can you help Commented Jul 24, 2020 at 18:30
• Use e.g. `28800.0/3600` or `28800::numeric/3600` instead of `28800/3600`
– user1822
Commented Jul 24, 2020 at 18:48

The expression:

``````select ((NULLIF(GREATEST(coalesce(28800/3600,0) - (coalesce(28382.6/3600,0) -
(coalesce(1920/3600,0) + (coalesce(1860/3600,0)))),0),0)))::character varying as mydatas
``````

evaluates to 0.11594...

You are getting it wrong at this step:

``````= select (28800/3600-(28382.6/3600-(1920/3600+1860/3600)));

= select (28800/3600-(28382.6/3600-(0.53333333333333+0.51666666666667))); -- False
``````

Because 1920/3600 = 0 and so is 1860/3600 due to integer division. You can fix your expression by making sure that one of the operands is not an integer type, example:

``````= select (28800.0/3600-(28382.6/3600-(1920.0/3600+1860.0/3600)));
``````

Fiddle

• @Lennart still I am not getting desired output Commented Jul 24, 2020 at 18:29
• @humbertoherrara, have a look at: dbfiddle.uk/… . Last cell evaluates to 1 which you claim is the expected result Commented Jul 24, 2020 at 18:35
• @Lennart; right, there are multiple issues. no casts, integer division, unclear requirements, `to_char()` pattern? Commented Jul 24, 2020 at 18:35
• @Lennart but I am getting data without .0, but there I can see that .0 is added Commented Jul 24, 2020 at 18:42
• @ErwinBrandstetter is there any other changes can I do? can you suggest please Commented Jul 24, 2020 at 18:48