Performing a SUM on a BIT field - any alternatives

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Performing a SUM on a BIT field - any alternatives

Author

Topic

imbored
Starting Member

2 Posts

Posted - 2007-09-04 : 14:39:40

I have just completed upsizing the back-end (tables only) of my Access Database to SQL 2005. On conversion I am having a problem with the BIT data type.

In Access, many of my fields were of the Yes/No type - being Boolean with results being either -1 or 0. In SQL these were converted to the BIT data type.

In some of my Front End Queries I "sum" the Yes/No fields together.

For example, I want to find all "Jobs" that had no "Issues", if a job had an issue it would have been checked-off with a "YES" = -1. Each job can have many issues/records.

What I did in my Access Query was to first "Group By" Job and second "Sum" the Yes/No Field. If there were no issues the answer would be 0. If there were issues the sum would be -1,-2 etc. Finally, I filter out only the "0's" to get my list of jobs with no issues.
Here is my SELECT Statement

SELECT Issues.ID, Sum(tblSignoffs.SignCheck2) AS SumOfSignCheck2
FROM Issues INNER JOIN tblSignoffs ON Issues.ID = tblSignoffs.IssueID
GROUP BY Issues.ID
HAVING (((Sum(tblSignoffs.SignCheck2))=0));

I believe SQL will not allow me to "Sum" fields of type "Bit"... can anyone think of an alternative way to go about this? Thanks.

David

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-04 : 14:49:27

You can use CASE:

sum(case when signCheck2 then 1 else 0 end)

OR convert/cast to an integer:

sum(cast (signCheck2 as integer))

- Jeff
http://weblogs.sqlteam.com/JeffS

imbored
Starting Member

2 Posts

Posted - 2007-09-04 : 16:28:28

Thanks Jeff... I used an IIF statement and it worked like a charm. Thanks for the pointer in the right direction.

David

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-04 : 16:29:56

since the value is 0 (your having clause...)
why not use count(*) instead?

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources