How to Count & Group SQL Results into Custom Group Categories?

(Apologies in advance - I'm not the best writer!

I work in a factory that makes electronic sensors.
We make these sensors in batches - and each batch has 10,000 Sensors.
We've made 30, 000 Lots as of today. (So that's 300 Million Sensors Processed)

We take a measurement for each sensor that measures how much the tail of the Sensor is bending.
If there is no bend in the tail (and it is parallel to the floor), the value is 0.
If the tail bends straight up at 90 degrees towards the ceiling, the value is 1.
And if the tail bends straight down at 90 degrees towards the floor, the value is -1.

The measurements are often not whole numbers and go to 8 decimal places however.
e.g. 0.18345171

The values get written to a table called SENSOR_INSPECTIONS in an Oracle Database.
When the Table is filtered by a specific Lot Number; it looks like the below.

| SENSOR # | VALUE      |
| 1        | 0.33827491 |
| 2        | 0.41810703 |

Is there a way for me to write an SQL Statement, where I perform a count and group the number of values based on my own custom categories?

For this example, I'd like to Count the number of values that equal 0, the number of values greater than 0, and the number of values less than 0.

Normally, I'd write an SQL Function like the below to do this.
But as there is are 8 Decimal places, this means there can be at least 100 Million distinct values/categories!

SELECT VALUE, COUNT(VALUE) as TotalRepetitions
FROM SENSOR_INSPECTIONS
GROUP BY VALUE;

Ultimately, I'd like a Table like the Below:

| Value | TotalRepetitions |
| 0     | 12567            |
| >0    | 56795            |
| <0    | 23456            |

You can use case expression to get the different groups:

SELECT VALUE_GROUP, COUNT(*) as TotalRepetitions
FROM
(
    SELECT CASE WHEN VALUE > 0 THEN '>0'
                WHEN VALUE < 0 THEN '<0'
                ELSE '0'
           END AS VALUE_GROUP
    FROM SENSOR_INSPECTIONS
)
GROUP BY VALUE_GROUP

One option might be conditional aggregation, e.g.

select 
  sum(case when value = 0 then 1 else 0 end) value_0,
  sum(case when value > 0 then 1 else 0 end) value_positive,
  sum(case when value < 0 then 1 else 0 end) value_negative
from sensor_inspections  

You can use the SIGN function to find out whether the value is positive, zero or negative:

SELECT SIGN(value) AS sign,
       COUNT(*) AS total_repetitions
FROM   table_name
GROUP BY SIGN(value)

Which, for the sample data:

CREATE TABLE table_name (sensor#, value) AS
SELECT LEVEL, +1 - DBMS_RANDOM.VALUE(0, 1) FROM DUAL CONNECT BY LEVEL <= 25 UNION ALL
SELECT LEVEL, 0 FROM DUAL CONNECT BY LEVEL <= 20 UNION ALL
SELECT LEVEL, -1 + DBMS_RANDOM.VALUE(0, 1) FROM DUAL CONNECT BY LEVEL <= 23;

Outputs:

If you want your strings for the value then you can wrap it in DECODE:

SELECT DECODE(SIGN(value), 1, '>0', -1, '<0', 0, '0') AS value,
       COUNT(*) AS total_repetitions
FROM   table_name
GROUP BY SIGN(value)

Which outputs:

fiddle

Use a case statement to group data into categories, then SUM or COUNT depending on what approach you want.

For test data

CREATE TABLE test_data  (sensor_id, val) AS
(
  select 1, 0.1 from dual UNION ALL
  select 1, 0.12 from dual UNION ALL
  select 1, 0.198 from dual UNION ALL
  select 1, -0.2 from dual UNION ALL
  select 1, 0 from dual UNION ALL
  select 2, -1 from dual
  
);

Either use one column per category

select sensor_id, 
       SUM(CASE WHEN val = 0 THEN 1 ELSE 0 END) as "0", 
       SUM(CASE WHEN val > 0 THEN 1 ELSE 0 END) ">0", 
       SUM(CASE WHEN val < 0 THEN 1 ELSE 0 END) "<0"
FROM test_data GROUP BY sensor_id;

 SENSOR_ID          0         >0         <0
---------- ---------- ---------- ----------
         1          1          3          1
         2          0          0          1

Or one category by row

select sensor_id, 
       CASE WHEN val = 0 THEN '0' WHEN val <0 THEN '<0' ELSE '>0' END as my_category,
       COUNT(*)
FROM test_data GROUP BY sensor_id, CASE WHEN val = 0 THEN '0' WHEN val <0 THEN '<0' ELSE '>0' END;

 SENSOR_ID MY   COUNT(*)
---------- -- ----------
         1 <0          1
         1 0           1
         1 >0          3
         2 <0          1