Count Instances of a number - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Count Instances of a number

Author

Topic

MBeal
Posting Yak Master

110 Posts

Posted - 2003-08-11 : 12:10:58

I have a table that stores survey results in 10 columns. Each column represents a question, with the answer being represented in a number between 0-5 (0= no response, and 5= poor). There are only 50 rows of data at this time, however this number will change. I want to count how many times a particular number (0-5) appears in each column. See below:

Question1 Question 2 Question 3 ...
0 3 1 0
1 15 9 5
2 20 18 15
3 10 16 12
4 2 2 3
5 0 4 15

I can't figure out how to do something like this... any suggestions?

MBeal

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-08-11 : 12:23:43

I think you can use this or something like it:

SELECT	'Question 1' as QuestionNum
	, Question1 'AnswerType'
	, count(*) 'AnswerCount'
FROM	myTable
GROUP BY Question1

UNION ALL

SELECT	'Question2', Question2, count(*)
FROM	myTable
GROUP BY Question2

UNION ALL

...etc...

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-08-11 : 12:26:28

The best answer is to change your schema so that each response is its own row, not column.

Jonathan
{0}

MBeal
Posting Yak Master

110 Posts

Posted - 2003-08-11 : 13:07:53

Jonathan,

You mentioned changing the schema. I agree that the schema is not ideal. However, the columns represent the questions. How would you suggest changing that?

MBeal

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-08-11 : 13:17:12

I think Jonathan meant that instead of having 10 columns for 10 questions perhaps you should have it like this :

QuestionID Answer
1 04
2 01
3 05

...etc

------------------------------------------------------------------------------
I enjoy using SQL Server but I am not part of the Microsoft fanboy club! NEVER!!

MBeal
Posting Yak Master

110 Posts

Posted - 2003-08-11 : 13:56:36

Awesome answers! Thank you both!

MBeal

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources