first thing that comes to mind is to unpivot to turn the columns into rows then group and count. I've already had issues with getting pivot/unpivot syntax correct, so I won't post any code.

the issue here is the DB design, there shouldn't be a column for each answer, they should be on different rows. what if you had 20,30,100,1000,10000000 answers? that'd be a lot of columns.

this post should altleast get someone else out there thinking of a full answer.