Getting the floor value of a number in SQLite?
I have a SQLite database with a table containing the scores of some league players in a bowling center. I'm trying to g开发者_运维问答et 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.
You can just use cast it to an integer. It will truncate it, which is equivalent to floor.
You can use round(value - 0.5) (seen here)
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
精彩评论