Getting the Floor Value of a Number in SQLite

Getting the floor value of a number in SQLite?

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

CEIL and FLOOR in SQLite

Formulas

Ceil : cast ( x as int ) + ( x > cast ( x as int ))
Take integer part of x and add 1 if decimal value is greater than 0

Floor : cast ( x as int ) - ( x < cast ( x as int ))
Take integer part of x and subtract 1 if decimal value is less than 0

Examples

Ceil :

SELECT (cast ( amount as int ) + ( amount > cast ( amount as int ))) AS amount
FROM SALES WHERE id = 128;
Floor :
SELECT (cast ( amount as int ) - ( amount < cast ( amount as int ))) AS amount
FROM SALES WHERE id = 128;

I have checked all the corner cases including negative number with MySQL ceil() and floor() functions.

Getting the ceil value of a number in SQLite

How about this?

select (case when x = cast(x as int) then cast(x as int)
             else 1 + cast(x as int)
        end)

Set floor/round value sql value

If there is no FLOOR function on that built-in server, try this:

SELECT CAST('67.896' AS int),  CAST('5.57' AS int)

How to get ceil value with custom digit number in SQLite?

SELECT cast(cast((UnitPrice - UnitPrice * DiscountPercent/100) * 100 as int) as real) / 100 FROM Product

Get integer part of number

SELECT FLOOR(value)

http://msdn.microsoft.com/en-us/library/ms178531.aspx

FLOOR returns the largest integer less than or equal to the specified numeric expression.

How can I calculate the median of values in SQLite?

Let's say that the median is the element in the middle of an ordered list.

SQLite (4 or 3) does not have any built-in function for that, but it's possible to do this by hand:

SELECT x
FROM MyTable
ORDER BY x
LIMIT 1
OFFSET (SELECT COUNT(*)
        FROM MyTable) / 2

---

When there is an even number of records, it is common to define the median as the average of the two middle records.
In this case, the average can be computed like this:

SELECT AVG(x)
FROM (SELECT x
      FROM MyTable
      ORDER BY x
      LIMIT 2
      OFFSET (SELECT (COUNT(*) - 1) / 2
              FROM MyTable))

Combining the odd and even cases then results in this:

SELECT AVG(x)
FROM (SELECT x
      FROM MyTable
      ORDER BY x
      LIMIT 2 - (SELECT COUNT(*) FROM MyTable) % 2    -- odd 1, even 2
      OFFSET (SELECT (COUNT(*) - 1) / 2
              FROM MyTable))

---

Related Topics