visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
315 experts, 1193 registered users, 1659 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

Databases :: MySql :: Redirection to 150 points question on MySQL
By: pazan U.S.A.  Date: 20/09/2003 00:00:00  English French  Points: 25 Status: Answered
Quality : Excellent
Hi experts,



I'm trying to figure out one thing, but no luck yet... So , I have table as following:



rating id

4 1

6 7

8 6

8 1

5 3

6 4

7 1

10 5

1 6



What i wanna do , i need to select top rated records.....which is i found hard to do. I know how to select one record:



SQL = "SELECT SUM(rating), COUNT(*) FROM table WHERE id=1"

RS.Open sql, Conn

avgRating = RS.Fields(0).Value / RS.Fields(1).Value

Response.Write avgRating



thats picks up the record "1" and writes the average rating. I need to show the top scores from average rating and fields from that id. I'm not sure if we can do it with sql statements...maybe we can't do it at all?



Cheers.
By: VGR Date: 20/09/2003 04:45:00 English  Type : Answer
1) you've to end the SQL phrase normally
2) you've to use the normal standard aliasing of SQL ([AS] is optional), not something like TotalRating=... which looks like a User-defined variable (so it should have @ prefixing its name)

3) having rewritten the aliases, here's the result of your query :

mysql> SELECT id, SUM(points) AS TotalRating, COUNT(*) as RecCount, (SUM(points)/COUNT(*) ) AS AvgRating FROM youjohnny group by id order by (SUM(points)/COUNT(*)) DESC;
ERROR 1111: Invalid use of group function

4) at this point, it's ***trivial*** that you misused the GROUP BY function. You should have used (for efficiency reasons) the alias of the formula, and not double-typed it.

mysql> SELECT id, SUM(points) AS TotalRating, COUNT(*) as RecCount, (SUM(points)/COUNT(*) ) AS AvgRating FROM youjohnny group by id order by AvgRating DESC;
+----+-------------+----------+-----------+
| id | TotalRating | RecCount | AvgRating |
+----+-------------+----------+-----------+
| 5 | 6000 | 1 | 6000.00 |
| 6 | 5000 | 1 | 5000.00 |
| 7 | 4500 | 1 | 4500.00 |
| 4 | 4000 | 1 | 4000.00 |
| 3 | 3000 | 1 | 3000.00 |
| 2 | 2000 | 1 | 2000.00 |
| 1 | 1000 | 1 | 1000.00 |
| 8 | 0 | 1 | 0.00 |
+----+-------------+----------+-----------+
8 rows in set (0.03 sec)


5) regards
By: VGR Date: 20/09/2003 06:58:00 French English  Type : Comment
d'une pierre deux coups :D

thanks

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 336.686850 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page