Honestly, it feels odd leveraging UNION to solve this problem, but upon further reflection it does provide a nice solution.
/* an (incomplete) subquery to be substituted into the main query below /*
SELECT id, count(*) AS positive_counts --positive values
UNION ALL
SELECT id, (- count(*)) AS negative_counts --negative of the count
You now have a table where IDs (can) repeat, but at most appear only twice, once with a positive count and once with a negative count. All you need to do to get your final answer is sum the positive and negative count together for each ID.
FROM (/*this is a subquery, in the FROM clause */) AS union_subquery (id, counted)
-- which requires an alias (name), which your query omitted and why you got the error you described.
-- I added column aliases here to emphasize that there are only two output columns
-- the name of the second column is originally taken from the first unioned query
-- (so, positive_counts, the name negative_counts is discarded once the union is complete.
-- But since writing sum(positive_counts) in the main query would be confusing I renamed
-- the column to just "counted" using the alias clause