Yet another way of doing this is by

• cross-joining your sign string values with your table
• counting conditionally your values according to the required sign

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

Output:

Check the demo here .

This might be an option:

• Make a query for each condition and union the queries using the "UNION ALL" keywork for mix the results of the queries in a single result.

Query:

SELECT 'negative' sign, COUNT(1) sign_count
FROM Numbers
WHERE number < 0
UNION ALL
SELECT 'neither' sign, COUNT(1) sign_count
FROM Numbers
WHERE number = 0
UNION ALL
SELECT 'positive' sign, COUNT(1) sign_count
FROM Numbers
WHERE number > 0;

Results:

See it working on sqlfiddle.

You can write conditions first(with cte) and then group based on those conditions

with _listcon as (

select  'negative'  as  sign,CAST(0x8000000000000000 AS bigint) /*min bigint*/ startNumber,-1 EndNumber union 
select  'positive'  as  sign,1 startNumber, CAST(0x7FFFFFFFFFFFFFFF AS bigint) /*max bigint*/   EndNumber union 
select  'neither'  as  sign, 0 startNumber,0 EndNumber 

)

select  sign, ISNULL( COUNT(a.number),0) sign_count
from Numbers a
right join _listcon b on a.number between startNumber and EndNumber
GROUP BY sign

Demo

SELECT signs.sign, 
       SUM(CASE WHEN Numbers.number IS NULL THEN 0 ELSE 1 END)
FROM (SELECT 'positive' AS [sign] 
      UNION ALL 
      SELECT 'negative' 
      UNION ALL 
      SELECT 'neither') AS signs
LEFT OUTER JOIN Numbers 
             ON (signs.sign = CASE WHEN number < 0 THEN 'negative' 
                                   WHEN number > 0 THEN 'positive' 
                                   ELSE 'neither' END)
GROUP BY signs.sign

Since you want all the signs values, you need to have a table with all of them in it. You can do a table valued subquery like the above or an actual or temporary table. But the idea is that if the value doesn't exist in your data, you are not going to get it in your output. Since the neither value is not in your data, you need to create a table that has it. Then we join to your actual table based on the sign of the number and get a count.

To explain the sum vs count thing, since this is outer join you'll get rows with Numbers.number equal to NULL for the neither case. So if you count them you get 1 instead of the desired 0. So I changed it to give each row a value of 0 or 1 and take the sum. So for the rows with NULL number I give them that value of 0 so they aren't counted and for the others a value of 1 so they are. If you had actual NULL values in your numbers table, you'd have to do the null check on a field that always has data and no nulls.

Edit: changed from count approach to sum to account for missing rows in join

This is a way to do it using CASE expression and UNPIVOT :

select count(case when number < 0 then 1 end) as 'negative',
       count(case when number > 0 then 1 end) as 'positive',
       count(case when number = 0 then 1 end) as 'neither'
from Numbers;

Will return :

negative    positive    neither
1           2           0

Then we unpivot it using this query :

with cte as (
    select count(case when number < 0 then 1 end) as 'negative',
           count(case when number > 0 then 1 end) as 'positive',
           count(case when number = 0 then 1 end) as 'neither'
    from Numbers
)
select sign, sign_count
from cte
unpivot
(
  sign_count
  for sign in (negative, positive, neither)
) u;

Result :

sign        sign_count
negative    1
positive    2
neither     0

Demo here