Count positives and negatives of a value

sign-counts.sql

--- A query that count the number of positive/zero/negatives of the column 'value'
--- and group it by the year-month
WITH t AS
  ( SELECT value,
           YEAR,
           MONTH
   FROM my_table AS l
   )
SELECT YEAR,
       MONTH,
       sum(CASE
               WHEN value > 0 THEN 1
               ELSE 0
           END) AS positive,
       sum(CASE
               WHEN value = 0 THEN 1
               ELSE 0
           END) AS zero,
       sum(CASE
               WHEN value < 0 THEN 1
               ELSE 0
           END) AS negative
FROM t
GROUP BY t.YEAR,
         t.MONTH
ORDER BY YEAR,
         MONTH