SQL Removing an average from SQL
Hi. I am trying to take the average out of this SQL statement but get various errors. How would the statement look without the averaging? Thanks
SELECT Categories.[Sub Category] As Result, Round(avg((CAST([Score] as Float) / 5 * 100)), 2) AS Score FROM Answers LEFT JOIN Categories ON Answers.Category = Categories.ID GROUP BY Categories.[Main Category], Categories.[Sub Category], Answers.Survey, Answers.ReviewerID, Answers.CompanyID HAVING (((Categories.[Main Category])='INTERPERSONAL'
SELECT Categories.[Sub Category] As Result, Round(avg((CAST([Score] as Float) / 5 * 100)), 2) AS Score FROM Answers LEFT JOIN Categories ON Answers.Category = Categories.ID GROUP BY Categories.[Main Category], Categories.[Sub Category], Answers.Survey, Answers.ReviewerID, Answers.CompanyID HAVING (((Categories.[Main Category])='INTERPERSONAL'
ASKER
Hi. Slight adjustment. If I change the following SQL Statement to the one below that I get the following error
The following error occured while executing the query:
Server: Msg 189, Level 15, State 1, Line 1
The round function requires 2 to 3 arguments.
Incorrect syntax near 'Categories'
SELECT Categories.[Sub Category] As Result, Round(avg((CAST([Score] as Float) / 5 * 100)), 2) AS Score, Questions.Question FROM (Answers LEFT JOIN Categories ON Answers.Category = Categories.ID) LEFT JOIN Questions ON Answers.Category = Questions.Category GROUP BY Categories.[Main Category], Categories.[Sub Category], Answers.Survey, Answers.ReviewerID, Questions.Question, Answers.CompanyID HAVING (((Categories.[Main Category])='INTERPERSONAL'
SELECT Categories.[Sub Category] As Result, Round([Score]/ 5 * 100), 2) AS Score, Questions.Question FROM (Answers LEFT JOIN Categories ON Answers.Category = Categories.ID) LEFT JOIN Questions ON Answers.Category = Questions.Category GROUP BY Categories.[Main Category], Categories.[Sub Category], Answers.Survey, Answers.ReviewerID, Questions.Question, Answers.CompanyID HAVING (((Categories.[Main Category])='INTERPERSONAL'
The following error occured while executing the query:
Server: Msg 189, Level 15, State 1, Line 1
The round function requires 2 to 3 arguments.
Incorrect syntax near 'Categories'
SELECT Categories.[Sub Category] As Result, Round(avg((CAST([Score] as Float) / 5 * 100)), 2) AS Score, Questions.Question FROM (Answers LEFT JOIN Categories ON Answers.Category = Categories.ID) LEFT JOIN Questions ON Answers.Category = Questions.Category GROUP BY Categories.[Main Category], Categories.[Sub Category], Answers.Survey, Answers.ReviewerID, Questions.Question, Answers.CompanyID HAVING (((Categories.[Main Category])='INTERPERSONAL'
SELECT Categories.[Sub Category] As Result, Round([Score]/ 5 * 100), 2) AS Score, Questions.Question FROM (Answers LEFT JOIN Categories ON Answers.Category = Categories.ID) LEFT JOIN Questions ON Answers.Category = Questions.Category GROUP BY Categories.[Main Category], Categories.[Sub Category], Answers.Survey, Answers.ReviewerID, Questions.Question, Answers.CompanyID HAVING (((Categories.[Main Category])='INTERPERSONAL'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks
Please post the error or output and what contain Score field.
Regards
a