SQL 2 counts with different filter

Question

I have a table and I need calculate two aggregate functions with different conditions in one statement. How can I do this?

Pseudocode below:

SELECT count(CoumntA) *< 0*, count(CoumntA) * > 0*
FROM dbo.TableA

Answer 1

select '< 0' as filter, COUNT(0) as cnt from TableA where [condition 1]
union
select '> 0' as filter, COUNT(0) as cnt from TableA where [condition 2]

Be sure that condition 1 and condition 2 create a partition on the original set of records, otherwise same records could be counted in both groups.

Answer 2

This is the same idea as tombom's answer, but with SQL Server syntax:

SELECT
    SUM(CASE WHEN CoumntA < 0 THEN 1 ELSE 0 END) AS LessThanZero,
    SUM(CASE WHEN CoumntA > 0 THEN 1 ELSE 0 END) AS GreaterThanZero
FROM TableA

Answer 3

For SQL Server, one way would be;

SELECT COUNT(CASE WHEN CoumntA<0 THEN 1 ELSE NULL END),
       COUNT(CASE WHEN CoumntA>0 THEN 1 ELSE NULL END)
FROM dbo.TableA     

Demo here.

Answer 4

As @tombom demonstrated, this can be done as a single query. But it doesn't mean that it should be.

SELECT
  SUM(CASE WHEN CoumntA < 0 THEN 1 ELSE 0 END)   AS less_than_zero,
  SUM(CASE WHEN CoumntA > 0 THEN 1 ELSE 0 END)   AS greater_than_zero
FROM
  TableA

The time when this is not so good is...
- There is an index on CoumntA
- Most values (50% or more feels about right) are exactly zero

In that case, two queries will be faster. This is because each query can use the index to quickly home in on the section to be counted. In the end only counting the relevant records.

The example I gave, however, scans the whole table every time. Only once, but always the whole table. This is worth it when you're counting most of the records. In your case it looks liek you're counting most or all of them, and so this is probably a good way of doing it.

Answer 5

SELECT
SUM(IF(CoumntA < 0, 1, 0)) AS lowerThanZero,
SUM(IF(CoumntA > 0, 1, 0)) AS greaterThanZero
FROM
TableA

Is it clear what's happening? Ask, if you have any more questions.

A shorter form would be

SELECT
SUM(CoumntA < 0) AS lowerThanZero,
SUM(CoumntA > 0) AS greaterThanZero
FROM
TableA

This is possible, since in MySQL a true condition is equal 1, a false condition is equal 0

EDIT: okay, okay, sorry, don't know why I thought it's about MySQL here.

See the other answers about correct syntax.

Answer 6

It is possible to do this in one select statement.

The way I've done it before is like this:

SELECT SUM(CASE WHEN ColumnA < 0 THEN 1 END) AS LessThanZero, 
       SUM(CASE WHEN ColumnA > 0 THEN 1 END) AS GreaterThanZero
FROM dbo.TableA 

This is the correct MS SQL syntax and I believe this is a very efficient way of doing it.

Don't forget you are not covering the case when ColumnA = 0!