Hi,
I am trying to average the values of 4 columns in my DB but instead of returning the average it returns the sum of these.
rs3 = statement3.executeQuery("SELECT AVG(Satisfaction+Quality+Communication+Delivery) FROM Ratings WHERE SupplierID='"+SupplierID+"' AND AuctionID='"+LotID+"'");
rs3.next();
out.print(rs3.getInt(1));
any ideas?
Errrrr because your only adding them together and not dividing by 4 to get the average?
I believe the AVG function averages the totals of a column... not across columns (at least as I understand it. I could be wrong.)
PS.... a Java forum really isn't the place to ask SQL questions lol.
> shouldn't the AVG do that?
No, it is an aggregate function; it returns the average of the column for all the rows:
SELECT AVG(a) FROM
(SELECT 1 a FROM DUAL
UNION ALL
SELECT 2 FROM DUAL
UNION ALL
SELECT 3 FROM DUAL)
AVG(A)
-
2
1 row selected
Message was edited by:
jbish
thanks for the info!
Im not sure i understand your sql statement tho?
The union statements basically make each seperate query into one resultset (which would represent a column) which the AVG function can then get the average of
i changed it to this:
rs3 = statement3.executeQuery("SELECT (Satisfaction+Quality+Communication+Delivery)/4 FROM Ratings WHERE SupplierID='"+SupplierID+"' AND AuctionID='"+LotID+"'");
rs3.next();
out.print(rs3.getDouble(1));
however, with the values 3,2,4,5 it gives the reult of 3.0 but should be 3.5.
any idea why?
Because the AVG function returns the result in the same datatype as the variables passed in.
You are passing in a load of integers (full numbers) and as such the AVG function returns the nearest whole number i.e. 3
Again I say this JAVA forum is not the place for SQL questions. Try a SQL forum
> Because the AVG function returns the result in the
> same datatype as the variables passed in.
>
The OP was not using the AVG function in the last post.
> You are passing in a load of integers (full numbers)
> and as such the AVG function returns the nearest
> whole number i.e. 3
>
The OP needs to say what type of database is being used; in Oracle this would work:
SELECT (2+2+3)/3 a FROM DUAL
A
-
2.33333333
1 row selected
> Again I say this JAVA forum is not the place for SQL
> questions. Try a SQL forum
A good suggestion.
