how do I calculate field info w/in a recordset?

# Thread: how do I calculate field info w/in a recordset?

1. Junior Member
Join Date
Dec 1969
Posts
4

## how do I calculate field info w/in a recordset?

Hi All,<BR><BR>I have a database that stores info from a survey. There are alot of multiple choice items that have ratings (1 to 4) 1 = poor, 4 = excellent. I have about 22 questions that all have values ranging from 1 to 4. How can I calculate percentages for a given recordset so that each question has a percentage for each value 1 to 4? Any help would be greatly appreciated.

2. Senior Member
Join Date
Dec 1969
Posts
7,686

## RE: how do I calculate field info w/in a recordset

SELECT AVG(Rating) as AverageRating FROM QuestionResults<BR>GROUP BY Question<BR><BR>This gives you the average rating for each question. <BR><BR>(You could do:<BR><BR>SELECT AVG(Rating) * 25 as AverageRating FROM QuestionResults<BR>GROUP BY Question<BR><BR>to get percentage (1 = 25%) ????)

3. Senior Member
Join Date
Dec 1969
Posts
96,118

I&#039;m not sure exactly what you are asking.<BR><BR>Do you mean the percentage of users who selected 1 and the percentage who selected 2 and ... ???<BR><BR>Or do you mean just the average rating for all users?<BR><BR>Or???<BR><BR>Give a small example of input and expected output.<BR><BR>

4. Junior Member
Join Date
Dec 1969
Posts
4

Hi Bill,<BR><BR>Sorry, I need to elaborate:<BR><BR>Each field in the database stands for a question in the survey and stores a value of either 1 = poor, 2 = fair, 3 = good, 4 = excellent.<BR><BR>When the recordset is generated, I want to be able to take the recordset (say 50 to 100 records) and compile each field like this: <BR><BR>Question 1: 1=10 2=15 3=30 4=22 (total responses = 77)<BR>so the percentages would be:<BR> 10/77 15/77 30/77 22/77 or<BR> 13% 19% 39% 29%<BR><BR>So the output should look something like this:<BR><BR> Poor Fair Good Excellent<BR>Question 1: 13% 19% 39% 29%<BR><BR>Hope that helps, I don&#039;t have any source code. Thanks for any help that you can give.

5. Senior Member
Join Date
Dec 1969
Posts
96,118

## That's tougher than you might think...

6. Junior Member
Join Date
Dec 1969
Posts
4

## RE: That's tougher than you might think...

Thanks Bill, I will give it a try. I believe that in most cases it will be under 100 records. Thanks for your help, I will let you know if it works.<BR><BR>--You

7. Senior Member
Join Date
Dec 1969
Posts
96,118

## It's probably fine up to 1000 records...

The only limitation is that the array form of the recordset has to fit in memory. But all those values will each eat up only 16 bytes. 21 questions times 16 bytes is 336 bytes per user answer. Even if we assume another 100 bytes or so per answer (e.g., for the user name, user id, whatever), that&#039;s only 500 bytes per answer. <BR><BR>So 1000 answers is about 500KB of memory needed for the array. That&#039;s a chunk, but not terribly out of line. If you had 1000 users simultaneously asking for the same ASP page, you might get in trouble, but...<BR><BR>Always crunch the memory numbers to see if what you are doing makes sense.<BR><BR>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•