CODEWITHSUNDEEP
sqlsql-serverselectcount
Revokez
Revokez

Reputation: 323

Select Count Where Values greater than 0 SQL Server

I'm trying to figure out how to return a select query showing a count of all values in a column that are greater than 0. Then in the next column show a count of all values that = 0.

Example:

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

Would return a result for the select query of:

NumberOfGreaterThan0    NumberThatEqual0

5                       2

Upvotes: 15

Views: 49735

Answers (2)

Mureinik
Mureinik

Reputation: 312354

You can use a couple of count functions over case expressions:

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

Upvotes: 4

Hart CO
Hart CO

Reputation: 34784

You can use conditional aggregates for this via CASE expression:

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 NULL values.

Upvotes: 41

Related Questions