By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Numbers(
id INT,
number INT
);
INSERT INTO Numbers VALUES
(0, 2),
(1, -1),
(2, 1);
SELECT * FROM Numbers
| id | number |
|---|---|
| 0 | 2 |
| 1 | -1 |
| 2 | 1 |
SELECT sign,
COUNT(CASE WHEN sign = 'positive' AND number > 0 THEN 1
WHEN sign = 'negative' AND number < 0 THEN 1
WHEN sign = 'neither' AND number = 0 THEN 1 END) AS sign_count
FROM (VALUES('positive'),('negative'),('neither')) AS signs(sign)
CROSS JOIN Numbers
GROUP BY sign
| sign | sign_count |
|---|---|
| positive | 2 |
| negative | 1 |
| neither | 0 |
Warning: Null value is eliminated by an aggregate or other SET operation.