Sqlite multiple number ranges

I have an sqlite table that looks like this:

time	number
2024-02-23 03:00:00	-0.0415385690329766
2024-02-23 03:15:00	-1.24717775905644
2024-02-23 03:30:00	-0.598224308602898
2024-02-24 03:00:00	-0.391209672798098
2024-02-24 03:15:00	-3.01903966597076
2024-02-24 03:30:00	-1.05561725758225
2024-02-25 03:00:00	0.134035229803659
2024-02-25 03:15:00	-2.0615858352579757
2024-02-25 03:30:00	0.0470381575095116
2024-02-25 03:45:00	0.072381723812783

Is it possible to count the numbers based on the first decimal place? I would like to see how many numbers are between multiple decimal places (for example: between -0.1 and -0.2 and so on)

edit: for example: 0.18, 0.139, 0.12 should belong to '0.1:0.2' group. other numbers like: 1.13, 1.173, 1.132 should belong to '1.1:1.2' group.

The result should look something like this:

range	count
-3.1:-3.0	1
-2.1:-2.0	1
-1.3-1.2	1
-1.2-1.1	0
-1.1-1.0	1
-1:-0.9	0
-0.9:-0.8	0
-0.8:-0.7	0
-0.7:-0.6	0
-0.6:-0.5	1
-0.5:-0.4	0
-0.4:-0.3	1
-0.3:-0.2	0
-0.2:-0.1	0
-0.1:0	1
0:0.1	2
0.1:0.2	1
0.2:0.3	0
0.3:0.4	0
0.4:0.5	0
0.5:0.6	0
0.6:0.7	0
0.7:0.8	0
0.8:0.9	0
0.9:1.0	0

I found this solution but it's not working: https://dba.stackexchange.com/questions/22491/how-do-i-calculate-count-for-a-range-without-needing-to-use-multiple-queries

SELECT
  case
    when number BETWEEN 0 AND -0.1 THEN '0:-0.1'
    when number BETWEEN -0.1 AND -0.2 THEN '-0.1:-0.2'
    when number BETWEEN -0.2 AND -0.3 THEN '-0.2:-0.3'
    when number BETWEEN -0.4 AND -0.5 THEN '-0.4:-0.5'
    when number BETWEEN -1 AND -2 THEN '-1:-2'
    else 'other numbers'
  end as `range`,
  COUNT(1) as count
from table1
group by `range`

Now I have this as a result so something really wrong with my query but I can't figure it out.

range	count
other numbers	10

db fiddle: https://dbfiddle.uk/S0I1MBaT

Solution

They should not be grouped together. eg: 0.1 and 0.12 and 0.135 should be counted to its own group: '0.1:0.2'. The other numbers eg: 1.12, 1.16, 1.173 belongs to the group '1.1:1.2'

All you need to do is round-off the numbers to 1-decimal-place.

Unfortunately, SQLite doesn't have a round() function that accepts a digits parameter, but you can work-around it by using floor() with a 10x premultiplication (and then dividing by 10 again afterwards).

For example, if t.number == 0.234
- ...then FLOOR( t.number ) == 0 - which we don't want
  - ...but FLOOR( t.number * 10 ) == 2 - which is better
    - ...so FLOOR( t.number * 10 ) / 10 == 0.2 - which is perfect

Like so:

WITH c AS (

    SELECT
        FLOOR( t.number * 10 ) / 10 AS roundedNumber,
        t.number,
        t.time
    FROM
        myTable AS t
)
SELECT
    c.roundedNumber,
    COUNT(*) AS "Count"
FROM
    c
GROUP BY
    c.roundedNumber
ORDER BY
    c.roundedNumber;

Because SQL's GROUP BY step is conceptually performed before the SELECT part, you'll need to use an inner-query or a CTE (my answer uses a CTE).

If you want to use a histogram-bin-style column name for the grouping key, then do this:

WITH c AS (

    SELECT
        FLOOR( t.number * 10 ) / 10 AS roundedNumber,
        t.number,
        t.time
    FROM
        myTable AS t
)
SELECT
    ( CAST( c.roundedNumber AS text ) || '-' || CAST( c.roundedNumber + 0.1 AS text ) ) AS "Range",
    c.roundedNumber,
    COUNT(*) AS "Count"
FROM
    c
GROUP BY
    c.roundedNumber
ORDER BY
    c.roundedNumber;

...should give you results like this:

Range	roundedNumber	Count
"0.1-0.2"	0.1	123
"0.4-0.5"	0.4	9
"1.2-1.3"	1.2	1

Note that there will be gaps in the output data - but you can fill those in by doing an INNER JOIN on a GENERATE_SERIES() table expression.