MS SQL - Count multiple values

[vjr]

Hi,I have created a table which contains various fields.ID (Key field), Date, Location, ServiceName, Q1, Q2, Q3, Q4... Q18I also have a form which users fill in and submit, and the values are parsed onto the table into the appropriate fields.All fields are a choice apart from ID and Date. All the Q fields have these options:Fully implemented, Partially implemented, Not implemented, Not Rated, N/AI want to generate a report from this information. So, in another form the user would input the Date to and from, Location and ServiceName and submit.Now i want a report that shows each questions average of each answer, so it would look something like this:Date Range______________Location______________ServiceName_X to X____________________Y______________________Z__________Fully implemented__Partially implemented__Not implemented__Not Rated__N/AQ1__________%_______________%_________________%____________%_____%Q2__________%_______________%_________________%____________%_____%Q3__________%_______________%_________________%____________%_____%Q...I've managed to do the top part of this result page (Date range, location and service name), but i need help in creating a query where i can select each field from Q1 to Q18 and find an average of Fully implemented, Partially implemented, Not implemented, Not Rated and N/A per each question.I hope i get the answer I'm looking for here and that my explanation of the problem is understandable, if not please let me know and i'll try again.

[aspnetguy]

assuming that your question values are numbers you can use the AVG function to get an average of the values

SELECT AVG(Q1) as 'Q1 Average' from yourTable'

[vjr]

Unfortunately they are not numbers.Each question can only be answered from the following list of options:Fully implementedPartially implementedNot implementedNot ratedNot ApplicableUnless in the form i put the value of each as 1, 2, 3, 4, 5 repectively which may be a bit of a problem now.

[aspnetguy]

adding the numebrs wouldn't work anyways as the AVG function gives you a matchmatical average of the actual numbers. This is a fairly complex report then. Does the report have to be completed with only SQL or can you use a programming language as well?Is it possible for you to post 10 sample records for your database for me to look at?

[justsomeguy]

How do you take an average from strings? What's the average of "Joe" and "Bob"?

[aspnetguy]

How do you take an average from strings? What's the average of "Joe" and "Bob"?

I think he means he wants to get the percentage of the total answers that each of the text answers received. i.e.Fully implemented 2/10 20%Partially implemented 1/10 10%Not implemented 3/10 30%Not rated 1/10 10%Not Applicable 3/10 30%Are these options stored in the database or are they just coded into the input form? If they are not in the database then you will have to hard code the answers into the query. VIJ how is your TSQL? There's going to be a lot of it. Get back to me on the last few questions and I'll try and give you something to poke at.

[vjr]

I think you got it aspnetguy. The example you gave is exactly what I want, but for each question, so there will be 18 of those.The options are on the input form drop down and not in the database. The form is PHP and that's what I'm using to create the results page.The database is only what i've mentioned above - one table. TSQL I'm no good at (Don't know what it is!), but then again i've managed to learn some MSSQL all by myself so i'm not afraid to try.Some sample records:FI = Fully implementedPI = Partially implementedNI = Not implementedNR = Not ratedNA = Not applicableID____Date______Location____ServiceName_____Q1_____Q2_____Q3...Q181____1/1/09_______London___________AA__________FI______PI_______PI...2____3/1/09_______London___________BA__________NR______NA_______PI...3____4/1/09_______London___________AB__________NA______FI_______PI...4____6/1/09_______London___________BB__________PI______PI_______PI...5____6/1/09_______London___________BC__________NR______FI_______PI...6____17/1/09______London___________CB__________NA______PI_______PI...7____18/1/09______London___________CC__________FI______FI_______PI...8____21/1/09______London___________DC__________NA______NR_______PI...9____21/1/09______London___________CD__________FI______PI_______PI...10___31/1/09______London___________DD__________PI______NA_______PI......Hope that makes sense. Cheers!