count columns in rows where value > 0

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)

count columns in rows where value > 0

Author

Topic

smh
Yak Posting Veteran

94 Posts

Posted - 2008-02-23 : 18:16:07

I have a detail table with rows as follows:

DetailID int
MasterID int
colcount int
B1 int
B2 int
B3 int
...
B10 int

I need to put into colcount the total count of columns in all the rows (for each masterID) that have a value > 0.

There can be 0 or more detail rows for each Master ID so I have to look at all the columns (B1...B10) in all the rows that have the same master ID

How can I do that?

Thanks

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-02-23 : 20:26:43

[Code]
Update [TableName]
Set colcount = b.RecordCount
FROM [TableName] a inner join (
Select Count(*) as RecordCount, MasterID
From [TableName]
Group by MasterID ) b
--WHERE b.RecordCount > 0

[/code]

You can add a where clause to filter for a count in excess of 0
by adding the WHERE clause which is commented out above.

EDIT: I thought you needed the row count...column count is a bit trickier...

How many columns and are all of them values?

You would have to do something funky like

(Case when column2 > 0 then 1 else 0 end) + (Case when column2 > 0 then 1 else 0 end)

in your select statement to get the count of COLUMNS where the column value > 0...messy.

Poor planning on your part does not constitute an emergency on my part.

smh
Yak Posting Veteran

94 Posts

Posted - 2008-02-23 : 20:59:37

Yes, I am looking at the count for columns and the only thing I could think of was using a case statement. I guess there is no way to access the columns except by their name, which means I have to use a 10 line case.

Hoped there was a better way.

Thanks

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-02-23 : 22:15:24

There may be a way to use their ordinal position to do this, but that is way nastier than doing the case statement.

You could set up a view that does individual case statements for true/false, you could then query the view for your totals...by just doing column1+column2 etc.

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources