Select Count where values ​​are greater than 0 SQL Server

Question

I am trying to figure out how to return a select query containing a count of all the values ​​in a column that is greater than 0. Then the next column indicates the number of all values ​​that are = 0.

Example:

ID  ColumnA
1   1    
2   2
3   1
4   2
5   0
6   0
7   1

Would return the result for a select request:

NumberOfGreaterThan0    NumberThatEqual0

5                       2

Answer 1

You can use conditional aggregates for this through expression CASE:

SELECT COUNT(CASE WHEN ColumnA > 0 THEN 1 END) AS NumberOfGreaterThan0 
      ,COUNT(CASE WHEN ColumnA = 0 THEN 1 END) AS NumberThatEqual0
FROM YourTable

This works because aggregate functions ignore values NULL.

Answer 2

You can use several functions countover expressions case:

SELECT COUNT(CASE WHEN columa > 0 THEN 1 ELSE NULL END) AS NumberOfGreaterThan0,
       COUNT(CASE columa WHEN 0 THEN 1 ELSE NULL END) AS NumberThatEqual0
FROM   my_table