Clever Geek Handbook

SQL 2 score with different filter

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

Below is the pseudocode:

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


source to share


6 answers


This is the same idea as the tombom query, 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
+3


source


As @tombom showed, this one can run as a single request. But that doesn't mean 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 it's not so good is ... - There is an index on the CoumntA website - Most values ​​(50% or more feel right) have exactly zero



In this case, two requests will be faster. This is because each query can use the index to quickly return home to the partition that needs to be counted. In the end, only counting the corresponding records.

However, the example I gave loops through the entire table every time. Only once, but always the entire table. It's worth it when you count most of the entries. In your case, it looks like what you think most or all of them are, and so this is probably a good way to do it.

+3


source


This can be done in a single select statement.

How I did it before:

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 find it to be a very efficient way to do it.

Don't forget that you are not closing the case when ColumnA = 0

!

+1


source 


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

Is it clear what's going on? Ask if you have any more questions.

The shorter form would be

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

This is possible because in MySQL the true condition is 1, the false condition is 0

EDIT: ok ok ok sorry dont know why i thought of this here.

See other answers for correct syntax.

0


source 


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]

Make sure condition 1 and condition 2 create a section in the original recordset, otherwise the same records can be counted in both groups.

0


source


For SQL Server, one of the ways is:

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 is here .

0


source






More articles:


All Articles