title: Truncation error in retrieving average value from H2 with Jooq
body: <p>I am trying to retrieve the average value form an H2 database with Jooq.</p>
  <p>Here is the line of code:</p>
  <pre class="lang-java prettyprint-override"><code>double dataSize = context.select(avg(PLC_DATA.LOAD_MAX_TOT))
                           .from(PLC_DATA)
                           .where(PLC_DATA.EVENT_INSTANT
                           .greaterThan(fromInstant)
                           .and(PLC_DATA.EVENT_INSTANT.lessThan(toInstant))
                           .and(PLC_DATA.ID.like(&quot;%&quot; + id + &quot;%&quot;)))
                           .fetchOneInto(Double.class);
  </code></pre>
  <p>The line of code works but the result does not have the decimals (i.e. 628.00)</p>
  <p>The data in the column <code>LOAD_MAX_TOT</code> is integer, do I have to cast the Integers in some way?</p>
  <hr />
  <p>As suggested by Simon Martinelli, the error is not in jooq but in my SQL Code. To get the result with the decimal part I need to cast the value before applying the average function.
  This is the correct SQL statment:</p>
  <pre class="lang-sql prettyprint-override"><code>select avg(cast(PUBLIC.PLC_DATA.LOAD_MAX_TOT as double)) 
     from PUBLIC.PLC_DATA 
     where (PUBLIC.PLC_DATA.EVENT_INSTANT &gt; cast(? as timestamp) and 
            PUBLIC.PLC_DATA.EVENT_INSTANT &lt; cast(? as timestamp) and 
            PUBLIC.PLC_DATA.ID like cast(? as varchar)) 
     {1: TIMESTAMP '2021-03-06 10:23:00', 2: TIMESTAMP '2021-03-06 12:32:00', 3: '%%'}
  </code></pre>
  <p>and the corresponding Jooq Java code:</p>
  <pre class="lang-java prettyprint-override"><code>context.select(avg(cast(PLC_DATA.LOAD_MAX_TOT, Double.class))).
         from(PLC_DATA).
         where(PLC_DATA.EVENT_INSTANT.greaterThan(fromInstant).
         and(PLC_DATA.EVENT_INSTANT.lessThan(toInstant)).
         and(PLC_DATA.ID.like(&quot;%&quot; + id + &quot;%&quot;))).
         fetchOneInto(Double.class)
  </code></pre>
  <p>The result is now:</p>
  <pre><code>Double@242 &quot;628.5714285714286&quot;
  value:628.571429
  </code></pre>