SQLTeam.com | Weblogs | Forums

Summing colum base on true or false in another column


#1

Hello,
I have table with many columns and I need to sum them base on true/false bit in another column. if the bit in Column A =1 then sum those rows
and if 0 then sum those rows.

Table name :: Table1
column A | Column B
1 | 10
1 | 5
0 | 20
0 | 6
1 | 2
0 | 3

Column A is bit column, I need to sum all rows in column B with correspond 1 in column A and sum all with correspond 0 and a Grand totals of both..

Example from table above
Sum1 = 10+5+2 (these values all have 1's in column A)
Sum2 = 20+6+3 ( all have 0's in column A )
Grand Total = Sum1 + Sum2

I would like some help with a SQL statement that will perform the above. any help will be appreciated. hopefully I explain what I am trying to do so someone can understand and help.

Thanks.


#2

select sum(case when ColA = 1 then ColB else 0 end) SumColA1, sum(case when ColA = 0 then ColB else 0 end) SumColA0, from Table1


#3

Select ColA, Sum(ColB) from table1 group by ColA


#4

That's better than mine...


#5

Personally I would want one row rather than two. The O/P also says "... and a Grand totals of both" which suggests to me:

select
   sum(case when ColA = 1 then ColB else 0 end) SumColA1,
   sum(case when ColA = 0 then ColB else 0 end) SumColA0,
   sum(ColB) AS GrantTotal
from
   Table1

#6

Thanks for the response, really appreciate it.