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.
| Author |
Topic |
|
karuna
Aged Yak Warrior
582 Posts |
 Posted - 2005-04-28 : 19:12:13
|
I have a table like this:TblACol1 Col2 Col3 Col4 Col5 Col6 Col7 Col8 Col9 Col101 0 0 0 1 0 0 0 0 00 0 0 0 0 0 0 0 0 10 1 0 1 0 0 0 1 0 0 All these are bit data types.I need to find the total of these columns which has a value of 1.As per the example above the result should be 6. How do I achieve this?Karunakaran___________It's better to be loved and lost, than ever to be loved... |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-04-28 : 19:16:45
|
| I don't suppose normalizing the table is an option? |
 |
|
|
karuna
Aged Yak Warrior
582 Posts |
Posted - 2005-04-28 : 19:22:14
|
| I cannot normalize :(. This is one temp table where I import the dump and mark the valid records based on certian business rules.Karunakaran___________It's better to be loved and lost, than ever to be loved... |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-04-28 : 19:29:25
|
| If it's a temp table, change the bit to tinyint, and then do this:SELECT Sum(col1+col2+col3+col4+col5+col6+col7+col8+col9+col10) FROM #myTable |
|
|
|
karuna
Aged Yak Warrior
582 Posts |
Posted - 2005-04-28 : 19:59:02
|
| Thanks RobI'll check this outKarunakaran___________It's better to be loved and lost, than ever to be loved... |
|
|
|
mpetanovitch
Yak Posting Veteran
52 Posts |
Posted - 2005-04-29 : 12:18:32
|
| Here is a way were you don't have to convert for each one or change your datatypes in your table.SELECT SUM(0 + col1 + col2 + col3 + col4 + col5 + col6 + col7 + col8 + col9 + col10) FROM #myTableYou need to add the 0 + first because SQL server will do a implicit converstion from bit to int.Mike Petanovitch |
|
|
|
|
|
|
Total Records