SQL AVG() function returns INT when multiple columns

EDIT: I did a mistake writing WHERE Users.Username=? instead of WHERE Answers.Username=?

If I use the following statement I get correct average 3.33:

public final String SELECT_USER_AND_AVGANSWERS_STMT =
"SELECT AVG(CAST(Answers.Rating AS FLOAT)) FROM Answers "
+ "WHERE Answers.Username=?";

Notice I am only selecting AVG. When I try select more columns it returns 4, as in it rounded it up.

public final String SELECT_USER_AND_AVGANSWERS_STMT = 
"SELECT Users.Username, Users.Nickname, AVG(CAST(Answers.Rating AS FLOAT)) "
+ "FROM Users, Answers "
+ "WHERE Users.Username=?"
+ "GROUP BY Users.Username, Users.Nickname";

Users Table:

"CREATE TABLE Users(Username VARCHAR(10) PRIMARY KEY,"
+ "Password VARCHAR(8) NOT NULL,"
+ "Nickname VARCHAR(20) NOT NULL,"
+ "Description VARCHAR(50),"
+ "Photo VARCHAR(4000))";

Answers Table:

"CREATE TABLE Answers(AnswerID INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,"
+ "QuestionID INTEGER NOT NULL,"
+ "SubmittedTime TIMESTAMP NOT NULL,"
+ "Text VARCHAR(300) NOT NULL,"
+ "Username VARCHAR(10) NOT NULL,"
+ "Rating INTEGER DEFAULT 0 NOT NULL)";

Using Java / Embedded Derby database.

Appreciate your help.

Solution

As you pointed out, you are trying to match the Users.Username in this query, but Answers.Username in your first query, so this is one problem! This should work:

SELECT Users.Username, Users.Nickname, AVG(CAST(Answers.Rating AS FLOAT))
FROM Users, Answers
WHERE Answers.Username=?
GROUP BY Users.Username, Users.Nickname

Something else that could be problematic is whether or not the aggregation happens before the CAST. To check this you could use:

SELECT UserName, NickName, CAST(AVG(AnswersRating) AS FLOAT)
FROM
(
    SELECT Users.Username As UserName, Users.Nickname As NickName, CAST(Answers.Rating AS FLOAT) As AnswersRating
    FROM Users, Answers
    WHERE Answers.Username=?
) AS Float_Table
GROUP BY UserName, NickName