CODEWITHSUNDEEP
sqlcount
user2982160
user2982160

Reputation: 1

Countif function in SQL

My sample file as below (four columns):

ID Balance1 Balance2 Balance3
xx    -1       -1       0
yy    -1        0       1

Each customer is unique. I want to count the number of negative values and positive values e.g.count negative for xx is 2 and for yy is 1 while positive value (more than 0) for xx is 0 and yy is 1.

I can use the COUNTIF function in Excel, but how can I use SQL statement?

Upvotes: 0

Views: 5117

Answers (2)

Besher
Besher

Reputation: 570

Try This

select id,   
sum (case when balance1 >0 then 1 else 0 end ) +
 sum (case when balance2 >0 then 1 else 0 end ) +
  sum (case when balance3 >0 then 1 else 0 end ) 
  as positive,

sum (case when balance1 <0 then 1 else 0 end ) +
 sum (case when balance2 <0 then 1 else 0 end ) +
  sum (case when balance3 <0 then 1 else 0 end )  as negative
from tABLE

group by id

Upvotes: 0

Dharmesh R. Patel
Dharmesh R. Patel

Reputation: 11

you should try this :

Select ID
    , Sum( Case When value < 0 Then 1 Else 0 End ) As Negatives
    , Sum( Case When value > 0 Then 1 Else 0 End ) As Positive
From sample_table
Group By ID

Upvotes: 1

Related Questions