Counting Occurence in multiple columns in 1 row

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)

Counting Occurence in multiple columns in 1 row

Author

Topic

SQLBeginner230
Starting Member

3 Posts

Posted - 2012-07-02 : 09:01:08

Hi,

I have data like the following

Table A

ID First_Status 2ndStatus 3rd Status
1 Complete Complete Complete
2 Complete Incomplete Complete
3 Incomplete Incomplete Complete

and so on...

I need to calculate for each ID what the count of complete or incomplete is.

That is for ID 1 complete count=3 and Incomplete count =0
ID 2 complete count =2 and Incomplete count=1
etc.

However there are more than just 2 statuses ( there are statuses other than complete and incomplete)

How can i count this for each row in the table

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-07-02 : 10:10:08

Are you able to change the database design?
It is a very bad idea to have its own column for each possible status.

No, you're never too old to Yak'n'Roll if you're too young to die.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2012-07-02 : 10:18:38

Yes webfred is correct.
but still you may try this -


SELECT	ID, 
		SUM((CASE WHEN Status1 = 'Complete' THEN 1 ELSE 0 END) + (CASE WHEN Status2 = 'Complete' THEN 1 ELSE 0 END) + (CASE WHEN Status3 = 'Complete' THEN 1 ELSE 0 END)) CompleteCount,
		SUM((CASE WHEN Status1 = 'InComplete' THEN 1 ELSE 0 END) + (CASE WHEN Status2 = 'InComplete' THEN 1 ELSE 0 END) + (CASE WHEN Status3 = 'InComplete' THEN 1 ELSE 0 END)) InCompleteCount
FROM tblStatus
GROUP BY ID

Vaibhav T

If I cant go back, I want to go fast...

SQLBeginner230
Starting Member

3 Posts

Posted - 2012-07-02 : 10:19:28

Hi,

Unfortunately the design is kind of unchangeable. The only way i could think of was bunch of If else statements but that would be just lots of code. I wanted to see if there was a simpler way.

Thanks!!

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2012-07-02 : 10:19:48

You can remove Group by and SUM if you want calculation row by row.

Vaibhav T

If I cant go back, I want to go fast...

SQLBeginner230
Starting Member

3 Posts

Posted - 2012-07-02 : 10:54:03

Thanks!! I will try that. Thank you again for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources