Classic ASP Discussion - SQL Statement????

Print Page | Close Window

SQL Statement????

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Classic ASP Discussion
Forum Description: Discussion on Active Server Pages (Classic ASP).
URL: https://forums.webwiz.net/forum_posts.asp?TID=3818
Printed Date: 30 March 2026 at 3:26am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: SQL Statement????

Posted By: choshour
Subject: SQL Statement????
Date Posted: 25 June 2003 at 9:12am

Trying to write an sql script to sort my teams by rating. Problem is I have to use the avg feature and distinct feature to get the average for each team. It then sorts it by teamname instead of rating.

rs.Open "SELECT DISTINCT TeamName, avg(rating) AS Average_rating FROM ratings GROUP BY teamname

It will not let me group by Average_rating
gives me This error
Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'TeamName' as part of an aggregate function

You can not use order by with DISTINCT

Replies:

Posted By: pmormr
Date Posted: 25 June 2003 at 10:17am

why don't you average everything after you select everything?

-------------
Paul A Morgan

http://www.pmorganphoto.com/" rel="nofollow - http://www.pmorganphoto.com/

Posted By: choshour
Date Posted: 25 June 2003 at 11:08am

How could you sort it then?

I figured out how to do it though. I used the max () feature and it worked.

rs.Open "SELECT DISTINCT max([TeamName])as Team, avg(rating) AS Average_rating FROM ratings GROUP BY teamname ORDER BY avg(rating) DESC"