Here's my weird AVG/rounding problem:

I used the following code:

SELECT Avg(Q_1) AS avgq1, Avg(Q_2) AS avgq2, course_date
FROM dbo.ostid_class_survey1
WHERE course_instructor = 'MMColParam' GROUP BY course_date
ORDER BY course_date DESC

It looked liked it worked until I checked the results on my calculator and they were wrong.

The code returns the following averages:
Avg(Q_1) AS avgq1 = 2.00
Avg(Q_2) AS avgq2 = 2.00

These are the actual answers to the questions, if you do the math they don't match the above numbers:
Q_1: 1,3,2,5,1 Average is: 2.4
Q_2: 2,4,5,1,1 Average is: 2.6

Any idea why it is rounding or how to tell it to stop?

Thanks!

Mitch

well actually, it doesn't look like it's rounding because if it was, 2.6 would become 3. the only things that I can think of, is that either the query isn't looking at the data you think it is. or the datatype that you're using is incorrect.

make sure (either in the RDBMS, or by doing some sort of repeat region to test) that the query is returning the records you think it is.
if it is, then check you datatypes, I don't know what DB/language you're using, but if it's something like JSP, make sure you're using a float or something (I don't actually know JSP, but I assume there's a float datatype<img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>

Joel Martinez

----------
Is this thing on?....

Joel,

You are absolutely correct, I verified the SQL table:
"DATA TYPE = Number" and "FIELD SIZE = BYTE"

BYTE does not allow DECIMALS. I asked the DB Admin to look at it, he confirmed that BYTE does not work and he tried DECIMAL which worked. Then he told me that he would NOT!!! change it to decimal because "he" thought it was not an eficient way to handle the problem. He then told me he could not help and that I had to figure something out.

Yes, I have escalated this, we both work for the same company and I have no idea why they are acting this way?

Anyway, I was wondering if there was a way to capture the numbers as a variable, and then manipulte the variable instead of the data from the SQL table. Does that make sense? I was thinking the variable would be independant of the BYTE issue?

Thanks!

Mitch

boy that sucks... this guy should try to be a bit more cooperative.

I think that the problem you're gonna have, is that it's stored in the table without the decimal info, so even if you get a recordset and manipulate it clientside, the data is getting corrupted as it enters the DB.

I would try to get that datatype changed, unless there are a billion other applications accessing this table (which could cause datatype mismatches if it's changed) it shouldn't matter.

or if he's being really abstinent about it, see if he'll at least add another column to the table with the right datatype. maybe you can use that...

Joel Martinez

----------
Is this thing on?....

Joel,

I should mention that the numbers are being entered via Radio Buttons with a range of 0 through 5.

That's the weird part, it seems that even though the numbers that are being stored are correct, the ASP page is still ignoring decimals? It's even more bizare that the Access query I created works fine?

That is why I was thinking if I could capture the number in a variable I would avoid the issues I am having.

Thanks,

Mitch

Joel,

This is what I came up with to solve the problem:

&lt;% Session("q_1sumvar" = (Recordset1.Fields.Item("sumq1".Value) %&gt;
&lt;% Session("q_1countvar" = (Recordset1.Fields.Item("countq1".Value) %&gt;
&lt;% Session("q_1avg" = (Session("q_1sumvar") / (Session("q_1countvar")%&gt;

I created 2 variables to capture the data, and 1 to get the average of them. It's the only thing I could think of, I guess it will get a little lengthy with 14 questions, but if the ADMIN won't budge, then this might be the way to go.

The one thing I noticed is that the above ONLY works if I display 1 record at a time, which is fine, I just have to figure out to get to the other records, like a NEXT RECORD button.

Thanks!

Mitch

ahh, I see, so it's whole numbers going in in the first place. well that makes it better.

as far as displaying them, I know it's kind of difficult to figure out exactly what you app is doing, but, instead of using session variables, just put it in a loop of some sort and printem all out:<pre id=code><font face=courier size=2 id=code>dim avg
do while not recordset1.eof
avg = Recordset1.Fields.Item("sumq1".Value / Recordset1.Fields.Item("countq1".Value

response.write avg & "&lt;br&gt;"
recordset1.movenext
loop</font id=code></pre id=code>this code will iterate through the recordset, and print out the average with a line break after it. of course you may want to reformat this to look nicer

hope you get it resolved.

Joel Martinez

----------
Is this thing on?....

Joel,

THanks!

Seeing that this is going into a neatly formatted table, would it be easier if I used the AS functions, as in:

response.write avg AS avg1

I am sure I did not put the AS function in the right place, but I think you understand what I want, could you please check?

Thanks for the help!!! I took the day off, so I will test it tomorrow.

Mitch

When in doubt...reboot!

ahh, I think you're misunderstanding that snippet,
avg is just a variable name, so when you do a response.write, it will just print the value onto the page.
if you want a title or something, you can do something like this:
response.write "average: " & avg

Joel Martinez

----------
set rs = conn.execute("SELECT answer FROM brain WHERE question = "& forumPost &"

Ah! That makes sense, I look forward to trying it tomorrow!

Mitch

When in doubt...reboot!