Count on different columns

Question

Post reply

Count on different columns

monzulu

Mr or Mrs. 500

Points: 528
More actions
May 1, 2010 at 4:36 pm

#221150

hi guys, i've got a table like this:
id - item - col1 - col2 - col3
1 - david -checked - unchecked - checked
2 - david -checked - checked - unchecked
3 - anne -unchecked -checked - checked
4 - peter -checked - checked - unchecked
5 - peter -unchecked - unchecked - unchecked
.
.
.
and i want to have the recordset like this(counting those with ticks on checkbox):
item - col1 - col2 - col3
david - 2 - 1 - 1
anne - 0 - 1 - 1
peter - 1 - 1 - 0
any tips guys? thanks in advance.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

DECLARE @data

TABLE (

id INTEGER PRIMARY KEY,

item VARCHAR(10) NOT NULL,

col1 VARCHAR(9) NOT NULL,

col2 VARCHAR(9) NOT NULL,

col3 VARCHAR(9) NOT NULL

);

INSERT @data

VALUES (1, 'david', 'checked', 'unchecked', 'checked'),

(2, 'david', 'checked', 'checked', 'unchecked'),

(3, 'anne', 'unchecked', 'checked', 'checked'),

(4, 'peter', 'checked', 'checked', 'unchecked'),

(5, 'peter', 'unchecked', 'unchecked', 'unchecked');

SELECT item,

COUNT(NULLIF(col1, 'unchecked')),

COUNT(NULLIF(col2, 'unchecked')),

COUNT(NULLIF(col3, 'unchecked'))

FROM @data

GROUP BY item

ORDER BY MIN(id);

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

dmoldovan SSCertifiable Points: 6334 More actions · Answer 2

Also check

http://www.sqlmag.com/blogs/PuzzledbyTSQL/tabid/1023/entryid/12611/Aggregate-Over-Columns.aspx

Paul White SSC Guru Points: 150467 More actions · Answer 3

dmoldovan (5/3/2010)

Also check

http://www.sqlmag.com/blogs/PuzzledbyTSQL/tabid/1023/entryid/12611/Aggregate-Over-Columns.aspx%5B/quote%5D

Seems to be solving a different problem from that asked in this thread though...?

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

dmoldovan SSCertifiable Points: 6334 More actions · Answer 4

Paul White NZ (5/3/2010)

dmoldovan (5/3/2010)
Also check
http://www.sqlmag.com/blogs/PuzzledbyTSQL/tabid/1023/entryid/12611/Aggregate-Over-Columns.aspx%5B/quote%5D
Seems to be solving a different problem from that asked in this thread though...?

It's a general overview of aggregating over several columns. For me it was a good resource.