CODEWITHSUNDEEP
sqlsql-server
Sai
Sai

Reputation: 221

SQL Server Query to count the number of Negative and Positive values in a column (group by)

Can you help me with the SQL query of counting the number of negative and positive values in the following table Balance column. Any help is much appreciated.

Attached the table image for your refeerence

I have used the following query but it doesn't combine the results

select  ACCT_GROUP + ' account has ' + cast (count(distinct Balance) as nvarchar(20)) + ' Negative Values' from #tmptrueup where Balance<0 group by ACCT_GROUP
union
select  ACCT_GROUP + ' account has ' + cast (count(distinct Balance) as nvarchar(20)) + ' Positive Values' from #tmptrueup where Balance>0 group by ACCT_GROUP

Upvotes: 0

Views: 3851

Answers (2)

FLICKER
FLICKER

Reputation: 6693

You need a CASE in group

select ACCT_GROUP
, SUM(case when Balance < 0 then 1 else 0 end) as NegativeCount
, SUM(case when Balance >= 0 then 1 else 0 end) as PositiveCount
from #tmptrueup
group by 
    case 
        when Balance < 0 then 1 
        else 0 
    end

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

You can use conditional aggregation for this:

select  ACCT_GROUP + ' account has ' + 
        cast (count(case when Balance < 0 then 1 end) as nvarchar(20)) + 
        ' Negative Values and ' +
        cast (count(case when Balance >= 0 then 1 end) as nvarchar(20)) +
         ' positive.'
from #tmptrueup 
group by ACCT_GROUP

Note: If you want to count distinct values then you can use:

count(distinct case when Balance < 0 then Balance end)

in place of

count(case when Balance < 0 then 1 end)

and do the same thing for positive values.

Upvotes: 2

Related Questions