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.

Languages :: ASP :: VBS :: is it possible with SQL select?
By: pazan U.S.A.  Date: 18/09/2003 00:00:00  English  Points: 300 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: puranik_p Date: 18/09/2003 19:02:00 English  Type : Comment
SELECT id, TotalRating = SUM(rating), RecCount  = COUNT(*) , AvgRating = (SUM(rating)/COUNT(*) )
FROM table
group by id
order by (SUM(rating)/COUNT(*) )
By: puranik_p Date: 18/09/2003 19:02:00 English  Type : Comment
correction..
order by (SUM(rating)/COUNT(*) )  DESC
By: pazan Date: 18/09/2003 19:12:00 English  Type : Comment
Hi puranik_p,
I tried your string, but it doesn't work. I dont understand what are these:  TotalRating,RecCount,
AvgRating? Is it just for me or if it's supposed to be there , then it will think that i have them fields
TotalRating,RecCount,AvgRating .............

Then i tried this:
SQL = "SELECT id SUM(rating) COUNT(*) (SUM(rating)/COUNT(*) )FROM table group by id order by (SUM(rating)/COUNT(*) )"

didn't work.

Any ideas?

Thx.
By: phL Date: 18/09/2003 19:45:00 English  Type : Comment
SQL = "SELECT id, SUM(rating) AS TotalRating, COUNT(*) AS RecCount, (SUM(rating)/COUNT(*)) AS AvgRating FROM table group by id order by (SUM(rating)/COUNT(*))"
By: pazan Date: 18/09/2003 19:55:00 English  Type : Comment
phL ,
still getting error .......mySQL syntax at line 1.........
By: phL Date: 19/09/2003 01:04:00 English  Type : Comment
It is working for me...  Specifically, I built the query in Access 2000 and used the following sql:

SELECT id, Count(*) AS CountOfid, Sum(rank) AS SumOfrank, Sum([rank])/Count(*) AS AvgOfRank
FROM Table2
GROUP BY id
ORDER BY Sum([rank])/Count(*) DESC;

Is your "id" field set to a character datatype by chance?
Could you post the full asp code that your are using?
Also, do a response.write on the sql string before your execute and we can look to see if there are any typos.
By: pazan Date: 19/09/2003 09:28:00 English  Type : Comment
This is what i have:
<%
Dim strConnection,MyConn
strConnection = "Driver={MySQL};server=domain.com;uid=username;pwd=password;database=data;"
Set MyConn = Server.CreateObject("ADODB.Connection")
MyConn.Open strConnection  
Dim RS,sql
       
set RS = Server.CreateObject("ADODB.Recordset")
SQL = "SELECT id, COUNT(*) AS RecCount, SUM(rating) AS TotalRating, SUM([rating])/COUNT(*) AS AvgRating FROM table GROUP by id order by SUM([rating])/COUNT(*) DESC;"

        RS.Open sql, MyConn     ###LINE 117###
         
         Response.Write "<img src="""& RS.fields("id")&""">"
         RS.Close
         Set RS = Nothing
%>

And this is the error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e09'

[TCX][MyODBC]You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '[rating])/COUNT(*) AS AvgRating FROM table GROUP by id or

/test.asp, line 117


And i'm using MySQL....Maybe we can't do it in MySQL?

Thx.
By: phL Date: 19/09/2003 09:44:00 English  Type : Comment
I am not familiar with the specifics of mySQL.

Try it with count(id) instead of count(*).
Try it with sum(rating) instead of sum([rating]).
Try naming your table something other than "table."
Is mySQL case sensitive?

Is the string you are building up on one line in your code or does it word wrap?

You can build up the variable like this (spaces before the final quote):

sql = ""
sql = sql & "SELECT id, Count(*) AS CountOfid, Sum(rating) AS SumOfrank, Sum([rating])/Count(*) AS AvgOfRating "
sql = sql & "FROM Table2 "
sql = sql & "GROUP BY id "
sql = sql & "ORDER BY Sum([rating])/Count(*) DESC;"

Try a simpler sql statement such as the following:

sql = ""
sql = sql & "SELECT id, Count(id)  "
sql = sql & "FROM Table "
sql = sql & "GROUP BY id "


Use response.write sql to output the actual sql statement to the screen.  Maybe there is a typo or a spacing issue.
By: puranik_p Date: 20/09/2003 19:50:00 English  Type : Comment
Its definitely a MySQL syntax error, which I think can't handle.
better post a question in MySQL area to convert the following query to MySQL.

This will work in Access and MS SQL. (TotalRating , etc are aliases that SQL understands)

SELECT id, TotalRating = SUM(rating), RecCount  = COUNT(*) , AvgRating = (SUM(rating)/COUNT(*) )
FROM table
group by id
order by (SUM(rating)/COUNT(*) )  DESC
By: VGR Date: 20/09/2003 20: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: pazan Date: 20/09/2003 21:31:00 English  Type : Comment
VGR ,Thanx man!
Brilliant answer.

Cheers.
By: VGR Date: 20/09/2003 21:57:00 English  Type : Comment
you're most welcome

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 322.379830 milliseconds

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