Count Column Values - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Count Column Values

Author

Topic

sqlslick
Yak Posting Veteran

83 Posts

Posted - 2011-11-22 : 10:03:37

Hey team,

I have a table in the following format:
STAFF, DATE, PATIENT, COL4, COL5, COL6, COL7
JOHN, 2011/11/01, BOB, TRUE, FALSE, FALSE, TRUE
MIKE, 2011/11/17, MARY, FALSE, TRUE, TRUE, TRUE

I want to extract all the columns above but have 2 extra columns at the end that display the total number of "TRUE" values and a percentage out of 3. Please see below:

STAFF, DATE, PATIENT, COL4, COL5, COL6, COL7, SCORE, PERCENTAGE
JOHN, 2011/11/01, BOB, TRUE, FALSE, FALSE, TRUE, 2, 50%
MIKE, 2011/11/17, MARY, FALSE, TRUE, TRUE, TRUE, 3, 75%

Thanks in advanced for your help!
J

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-22 : 10:07:44

[code]
SELECT STAFF, DATE, PATIENT, COL4, COL5, COL6, COL7,
CASE WHEN Col4='TRUE' THEN 1 ELSE 0 END +
CASE WHEN Col5='TRUE' THEN 1 ELSE 0 END +
CASE WHEN Col6='TRUE' THEN 1 ELSE 0 END +
CASE WHEN Col7='TRUE' THEN 1 ELSE 0 END AS SCORE,
(CASE WHEN Col4='TRUE' THEN 1 ELSE 0 END +
CASE WHEN Col5='TRUE' THEN 1 ELSE 0 END +
CASE WHEN Col6='TRUE' THEN 1 ELSE 0 END +
CASE WHEN Col7='TRUE' THEN 1 ELSE 0 END ) *100.0/4 AS PERCENTAGE
FROM table
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlslick
Yak Posting Veteran

83 Posts

Posted - 2011-11-22 : 10:33:48

Unbelievable! Excellent!! Thank you so much!!! The immediate response is very much appreciated!!!!

Have a great day!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-22 : 10:48:09

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources