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

Question

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

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 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 INSPECTIONS
GROUP BY VALUE;

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

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

Answer 1

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 INSPECTIONS
) DT
GROUP BY VALUE_GROUP