Menu
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.
596
SQL Server FLOOR() FunctionThe FLOOR() function returns the largest integer value that is smaller than or equal to a number. Tip: Also look at the CEILING() and ROUND() functions.
A column that uses INTEGER affinity behaves the same as a column with NUMERIC affinity. The difference between INTEGER and NUMERIC affinity is only evident in a CAST expression: The expression "CAST(4.0 AS INT)" returns an integer 4, whereas "CAST(4.0 AS NUMERIC)" leaves the value as a floating-point 4.0.
SQLite Storage Classes INTEGER – any numeric value is stored as a signed integer value (It can hold both positive and negative integer values). The INTEGER values in SQLite are stored in either 1, 2, 3, 4, 6, or 8 bytes of storage depending on the value of the number.
You can just use cast it to an integer. It will truncate it, which is equivalent to floor.
184
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
