0

I have a query which relies on avg() using sqlite-jdbc to connect to a sqlite database. However when my query runs I get an answer of 0.0 for the average, but when I run the same query elsewhere (portable database viewer) I get the expected (non-zero) value. Why?

Column being averaged is a list of integers.

SELECT COUNT(col) as count, AVG(col) as average FROM table WHERE <many-clauses-here>

The count returns correctly.

Values being averaged: "54", "56", "66", "53"

The file format is sqlite, but the way it is styled is in the spec given by ddf spec though this should not be relevant

Alastair
  • 338
  • 2
  • 11
  • Can you show us the query and the data involved? There could be a rounding issue at work here. – Tim Biegeleisen Mar 15 '16 at 03:29
  • That is something I would have never thought of. This is for work so I will check tomorrow against other sets of values as well. – Alastair Mar 15 '16 at 03:36
  • Probably a different database. – CL. Mar 15 '16 at 09:59
  • A second set of values also produces 0.0 while the database viewer produces a non-zero value. Could sqlitejdbc be misinterpreting type and thinking they're strings when they're actually longs? – Alastair Mar 15 '16 at 14:26

1 Answers1

0

After I tried using a regular statement instead of a PreparedStatement, I found it worked as intended. I was using JDBC incorrectly as described here - I'm newish to JDBC (haven't used it in ages) so I made a rookie mistake, but it manifested itself so oddly.

Community
  • 1
  • 1
Alastair
  • 338
  • 2
  • 11