30

I have a SQLite database with a table containing the scores of some league players in a bowling center. I'm trying to get the average of the Score column for each player ID. The problem with this is I only need the whole part of the average, and it should not be rounded (example: an average of 168.99 should return 168, not 169).

When trying to find something like this on Google, I only found solutions for SQL Server and some others, but not SQLite, so if anyone can help me with this, I'd really appreciate it!

So far, I'm using ROUND(AVG(s1.Score),2) and I'm truncating the extra part in my Java program that uses the database by converting it to a String, removing the unwanted part and then casting it to an Integer, but I'd rather do it all in SQL if possible.

NearHuscarl
  • 66,950
  • 18
  • 261
  • 230
Jumbala
  • 4,764
  • 9
  • 45
  • 65

3 Answers3

50

You can just use cast it to an integer. It will truncate it, which is equivalent to floor.

Derek
  • 21,828
  • 7
  • 53
  • 61
  • 22
    `cast(x as int)` is the same as `floor(x)` only if `x >= 0`. – mu is too short Aug 20 '11 at 02:39
  • 4
    @Adam: Something like `case when x >= 0 then cast(x as int) when cast(x as int) = x then cast(x as int) else cast(x - 1.0 as int) end` should work even though it is rather ugly. I'd probably try to [add a proper `floor` function](http://www.sqlite.org/c3ref/create_function.html) if I really needed this though. – mu is too short Aug 20 '11 at 02:53
  • @Adam: The quick and simple `cast` is a good solution for `x >= 0` (which should be the case for bowling scores, right?), just be aware where the holes are. – mu is too short Aug 20 '11 at 03:13
20

You can use round(value - 0.5) (seen here)

ded'
  • 664
  • 4
  • 11
  • 4
    +1 for completeness. This solution would also work, but like the other posted answer, it only works for x >= 0. For instance, floor(-2.8) should return -3, but using your solution, floor((-2.8 - 0.5)) would return -4. – Jumbala Jul 18 '14 at 13:57
  • the answer mentions round, not floor, and it also works for negative values – Ovidiu S. Oct 19 '21 at 10:54
  • 1
    it's so weird that even in 2022 mainstream database systems like SQLite are not able to correctly handle super basic things like proper rounding – Sliq Sep 18 '22 at 12:51
  • strange result for `round(2102.7 * 100 - 0.5)` that is `210269` – MSL Dec 06 '22 at 09:15
1

From version 3.35.0+, you can use the floor() function to round down the number:

SELECT
  floor(10.0),    -- 10
  floor(10.1),    -- 10
  floor(10.5),    -- 10
  floor(10.9999), -- 10
  floor(-10.0),   -- 11
  floor(-10.1),   -- 11
  floor(-10.5),   -- 11
  floor(-10.9999) -- 11
NearHuscarl
  • 66,950
  • 18
  • 261
  • 230
  • 2
    FLOOR() is not a built-in function. It is one of math functions() which require a custom build of SQLite: https://www.sqlite.org/compile.html#enable_math_functions – forpas Nov 24 '21 at 18:00