CODEWITHSUNDEEP
sqlsql-serversql-server-2008aggregate-functions
Yagnesh.Dixit
Yagnesh.Dixit

Reputation: 318

Sum of single column for different conditions

I have two columns in my table colum#1 is varchar(MAX) and column#2 is int.

In column#2 I have negative and positive entries I want sum of positive entries in one column and sum of negative entries in other column in my select query

To achieve this I did

SELECT SUM(atbl.M),SUM(atbl.p)    
from (select M=case when column#2<0 
                    then column#2 else 0 end,
             P=case when column#2 > 0 
                    then column#2 else 0 end 
        from testTable) atbl

or

select SUM(case when column#2<0 then column#2 else 0 end) as M
      ,SUM(case when column#2 > 0 then column#2 else 0 end) as P 
 from testTable

Is there any better way to achieve this.

Upvotes: 1

Views: 2922

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125214

If you do it frequently on that column then use a view:

create view testView as
select
    sum(case when column#2 < 0 then column#2 else 0 end) as M,
    sum(case when column#2 > 0 then column#2 else 0 end) as P
from testTable

Upvotes: 1

Related Questions