SQL Counting function

[smallinov]

I need help using the SQL counting function with SQL server 2000. Here is what I have been trying to get it to do and I have been failing.

I have a table that has 8 rating columns inside of it. By rating columns I mean that these columns store a rating number from 1 to 5 that rates personel on how well they are doing their jobs. I want to be able to use one sql statement that will give me 40 fields back (8x5) so that I can have a count of how many 1's and 2's etc... each person has. Is this do-able? This code will work for one of the columns but I want to be able to do all 8 at once.

SELECT tr_experience, COUNT(tr_trainer) AS Expr1
FROM evaluation
WHERE (tr_trainer = 3)
GROUP BY tr_experience

If anyone has any answers for me they would be greatly appreciated!

Thanks,
Ryan Smallegan
Web Developer

http://www.davenport.edu

 

[zhonghandle]

I don't know if the following link can help you, but I have had the same problem, and this site helped alot.

http://www.swynk.com/faq/sql/sqlfaq_development.asp#variable

and then go to "How can i issue a SQL command that uses a variable for the tablename, columns etc.?"
and then use a variable to represent your query and do a loop.

 

[tlbroadbent]

Try something like the following.

SELECT
tr_experience,
tr_trainer_1=sum(Case
When tr_trainer = 1 Then 1 Else 0 End),
tr_trainer_2=sum(Case
When tr_trainer = 2 Then 1 Else 0 End),
tr_trainer_3=sum(Case
When tr_trainer = 3 Then 1 Else 0 End),
tr_trainer_4=sum(Case
When tr_trainer = 4 Then 1 Else 0 End),
tr_trainer_5=sum(Case
When tr_trainer = 5 Then 1 Else 0 End),
second_rating_1=sum(Case
When second_rating = 1 Then 1 Else 0 End),
second_rating_2=sum(Case
When second_rating = 2 Then 1 Else 0 End),
second_rating_3=sum(Case
When second_rating = 3 Then 1 Else 0 End),
second_rating_4=sum(Case
When second_rating = 4 Then 1 Else 0 End),
second_rating_5=sum(Case
When second_rating = 5 Then 1 Else 0 End),
.
.
.
eighth_rating_1=sum(Case
When eighth_rating = 1 Then 1 Else 0 End),
eighth_rating_2=sum(Case
When eighth_rating = 2 Then 1 Else 0 End),
eighth_rating_3=sum(Case
When eighth_rating = 3 Then 1 Else 0 End),
eighth_rating_4=sum(Case
When eighth_rating = 4 Then 1 Else 0 End),
eighth_rating_5=sum(Case
When eighth_rating = 5 Then 1 Else 0 End),
FROM evaluation
GROUP BY tr_experience
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.