Systems Engineering and RDBMS

Summation over a bit data type column

Posted by decipherinfosys on January 12, 2011

One of the readers asked this question: “I have inherited a legacy application in which one of the columns in a table is declared as a bit data type.  I need to find the total sum/number of the records which have a value of 1 vs 0 vs Null and need to get it summed across different months.  I was trying to use SUM() aggregate function over that field and got an error: Operand data type bit is invalid for sum operator. What is an alternate/right way of getting this information using SQL?

There are a couple of ways to do this.   Here is a simple script to illustrate it:

/*
Declare a table variable with a datetime column and a bit column and populate it with values
*/
declare @test table (create_date_time datetime, col1 bit)
insert into @test values ('12/31/2010', 1), ('12/19/2010', 0), ('01/10/2011', 1), ('01/11/2011', 1), ('01/12/2011', 0), ('01/12/2011', null)
/*
Display the records
*/
select * from @test
/*
This statement will run into the 8117 error message
*/
select SUM(col1), AVG(col1) from @test where col1 = 1


/*
Get the counts using the case statement and also show how to do the SUM() over a bit data type
column by casting it first
*/
select
YEAR(create_date_time) as YEAR_CDT
, MONTH(create_date_time) as MONTH_CDT
, COUNT(case when col1 = 1 then 1 else null end) as CNT_1
, COUNT(case when col1 = 0 then 1 else null end) as CNT_0
, SUM(CAST(COL1 AS TINYINT)) as CNT_Second_Mthd_1
, COUNT(*) as TOTAL_CNT
from @test
group by YEAR(create_date_time), MONTH(create_date_time)

And here is the output:

/*All the records*/

create_date_time                      col1
2010-12-31 00:00:00.000               1
2010-12-19 00:00:00.000               0
2011-01-10 00:00:00.000               1
2011-01-11 00:00:00.000               1
2011-01-12 00:00:00.000              0
2011-01-12 00:00:00.000              NULL

/*Data from the SQL Statement*/

YEAR_CDT    MONTH_CDT    CNT_1    CNT_0    CNT_Second_Mthd_1    TOTAL_CNT
2011                1        2        1                    2            4
2010               12        1        1                    1            2

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: