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?
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
{} button - it keeps the layout as you wrote it and allows the highlighter a chance to prettify the code.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
Id,Balanceand whatever the name is for the data currently being embedded in your column names of 1, 2 and 3.